Wednesday, December 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.

Friday, December 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.