pointers, solutions and scripts for the SQL DBA
Not intended to replace common sense

12/15/2010

A word on the decimal data type

Was inspired to write up a few notes on the SQL Server data type.  This is pretty basic stuff, but everyone should know it.
If you declare a column as DECIMAL, it will default to the precision and scale 18, 0.  Not very useful, is it?  You might as well have used a bigint column to store integer values like that.  So first off, when you declare a decimal column, always give it precision and scale.

12/13/2010

Review: SQL Pocket Guide By Jonathan Gennick

The SQL Pocket Guide is a cross-platform reference guide for SQL standard syntax, covering Oracle, DB2, SQL Server, MySQL, and PostgreSQL.

Surely this book was quite an undertaking for the author.  The book provides a good amount of platform-specific detail when necessary.  For example, commendable is the inclusion of the importance of SET XACT_ABORT {ON|OFF} on SQL Server transactions, or caveats about InnoDB vs non-transactional tables in MySQL.  This is good detail that shows the author clearly has experience and insight which provide value to the reader.

Regularly, the author breaks a topic down by platform, which is helpful in navigation.  While the book provides syntax and examples on standard ANSI SQL statements in each platform, it avoids comparing and contrasting usage except when necessary.  This is a good thing, I can't see why the author need to include full cross references of simple syntax.

Sometimes though, detail is lacking. For example, the LAG and LEAD functions are implicitly described as supported by SQL Server when they are not.  This isn't necessarily errata, but is potentially confusing.

What would be considered errata is the incorrect note that SQL Server supports the TRIM string function. This wasn't the only errata I found and submitted in a few hours of skipping back and forth through the book, as one would typically make use of it.



In the interest of full disclosure, this blog was provided a free electronic (PDF) copy of the book through O'Reilly media's Blogger Review Program.  This review was not otherwise solicited or compensated from O'Reilly, and the opinions of the review are the opinion of its author.

I review for the O'Reilly Blogger Review Program

12/03/2010

Script TSQL Server Level security

Here's a handy script that's part of my toolbox everywhere I go.  Scripts out and identifies basic server level security objects, and generates a tsql statement to recreate the objects.

Note that this script only works on SQL 2005 or above.  This is far from an official script, so caveat emptor.

I welcome to any and all feedback on these scripts.  I also have a database-level security script here.
SELECT @@SERVERNAME

--create windows logins
select 'CREATE LOGIN ['+ name +'] FROM WINDOWS WITH DEFAULT_DATABASE=['+default_database_name+'], DEFAULT_LANGUAGE=['+default_language_name+']' 
from sys.server_principals
where type in ('U','G')


--script out SQL logins
--http://support.microsoft.com/kb/918992


--Server level roles
SELECT DISTINCT
 QUOTENAME(r.name) as server_role_name, r.type_desc, QUOTENAME(m.name) as principal_name, m.type_desc 
, TSQL = 'EXEC master..sp_addsrvrolemember @loginame = N''' + m.name + ''', @rolename = N''' + r.name + ''''
FROM sys.server_role_members AS rm
inner join sys.server_principals r on rm.role_principal_id = r.principal_id
inner join sys.server_principals m on rm.member_principal_id = m.principal_id
where r.is_disabled = 0 and m.is_disabled = 0
and m.name not in ('dbo', 'sa', 'public')
and m.name <> 'NT AUTHORITY\SYSTEM'


--Server Level Security
SELECT rm.state_desc, rm.permission_name, principal_name = QUOTENAME(u.name),  u.type_desc
,  TSQL = rm.state_desc + N' ' + rm.permission_name + N' TO ' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))
FROM sys.server_permissions rm
inner join sys.server_principals u 
on rm.grantee_principal_id = u.principal_id
where u.name not like '##%' 
and u.name not in ('dbo', 'sa', 'public')
order by rm.permission_name, u.name
update 11/4/2013: Changed formatting to new style, include link to script out hashes for sql logins, other updates since 2010.

Script TSQL Database-level security

Here's a handy script that's part of my toolbox everywhere I go.  Scripts out and identifies basic database level security objects, and generates a tsql statement to recreate the objects. 

Note that this script only works on SQL 2005 or above.  This is far from an official script, so caveat emptor.

I welcome to any and all feedback on these scripts.  I also have a server-level security script here.

--Run the below on each database for database-level security.

SELECT DB_NAME() as Database_Name

--Database Level Roles
SELECT DISTINCT
     QUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc
,    TSQL = 'EXEC sp_addrolemember @membername = N''' + d.name COLLATE DATABASE_DEFAULT + ''', @rolename = N''' + r.name + ''''
FROM sys.database_role_members AS rm
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals d on rm.member_principal_id = d.principal_id
where d.name not in ('dbo', 'sa', 'public')

--Database Level Security
SELECT     rm.state_desc
     ,   rm.permission_name
    ,   QUOTENAME(u.name) COLLATE database_default
    ,   u.TYPE_DESC
     ,   TSQL = rm.state_desc + N' ' + rm.permission_name + N' TO ' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))   
FROM sys.database_permissions AS rm
     INNER JOIN
     sys.database_principals AS u
     ON rm.grantee_principal_id = u.principal_id
WHERE rm.major_id = 0
and u.name not like '##%'
and u.name not in ('dbo', 'sa', 'public')
ORDER BY rm.permission_name ASC, rm.state_desc ASC

--Database Level Explicit Permissions
SELECT     perm.state_desc
    , perm.permission_name
    ,   QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
       + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name COLLATE DATABASE_DEFAULT) + ')' END AS [Object]
     , QUOTENAME(u.name COLLATE database_default) as Usr_Name
    ,   u.type_Desc
    , obj.type_desc
    ,  TSQL = perm.state_desc + N' ' + perm.permission_name
           + N' ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
           + N' TO ' + QUOTENAME(u.name COLLATE database_default)
FROM sys.database_permissions AS perm
     INNER JOIN
     sys.objects AS obj
     ON perm.major_id = obj.[object_id]
     INNER JOIN
     sys.database_principals AS u
     ON perm.grantee_principal_id = u.principal_id
     LEFT JOIN
     sys.columns AS cl
     ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
where
     obj.name not like 'dt%'
and obj.is_ms_shipped = 0
and u.name not in ('dbo', 'sa', 'public')
ORDER BY perm.permission_name ASC, perm.state_desc ASC
Or, wrap the whole thing in a msforeachdb:
exec sp_msforeachdb 'use [?]; 
SELECT DB_NAME() as Database_Name

--Database Level Roles
SELECT DISTINCT
 QUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc
, TSQL = ''EXEC sp_addrolemember @membername = N'''''' + d.name COLLATE DATABASE_DEFAULT + '''''', @rolename = N'''''' + r.name + ''''''''
FROM sys.database_role_members AS rm
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals d on rm.member_principal_id = d.principal_id
where d.name not in (''dbo'', ''sa'', ''public'')

--Database Level Security
SELECT  rm.state_desc 
 ,   rm.permission_name 
    ,   QUOTENAME(u.name) COLLATE database_default
    ,   u.TYPE_DESC
 ,   TSQL = rm.state_desc + N'' '' + rm.permission_name + N'' TO '' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))    
FROM sys.database_permissions AS rm
 INNER JOIN
 sys.database_principals AS u
 ON rm.grantee_principal_id = u.principal_id
WHERE rm.major_id = 0
and u.name not like ''##%'' 
and u.name not in (''dbo'', ''sa'', ''public'')
ORDER BY rm.permission_name ASC, rm.state_desc ASC

--Database Level Explicit Permissions
SELECT perm.state_desc
    , perm.permission_name 
    ,   QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name) 
       + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ''('' + QUOTENAME(cl.name COLLATE DATABASE_DEFAULT) + '')'' END AS [Object]
 , QUOTENAME(u.name COLLATE database_default) as Usr_Name
    ,   u.type_Desc
    , obj.type_desc
    ,  TSQL = perm.state_desc + N'' '' + perm.permission_name 
  + N'' ON '' + QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name) 
  + N'' TO '' + QUOTENAME(u.name COLLATE database_default)

FROM sys.database_permissions AS perm
 INNER JOIN
 sys.objects AS obj
 ON perm.major_id = obj.[object_id]
 INNER JOIN
 sys.database_principals AS u
 ON perm.grantee_principal_id = u.principal_id
 LEFT JOIN
 sys.columns AS cl
 ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
where 
 obj.name not like ''dt%''
and obj.is_ms_shipped = 0
and u.name not in (''dbo'', ''sa'', ''public'')
ORDER BY perm.permission_name ASC, perm.state_desc ASC
';
Update 11/4/2013: Changed formatting to new style, included a foreachdb version to hit all databases.