Script to Retrieve Security Information – SQL Server 2012 (Denali) and above

SQL Server 2012 codenamed SQL Server Denali is on the way to market by 2012. As you all know SQL Server 2012 has new features related to security, we have contained database where you are allowed to create contained users and the next feature is server level roles, other security features are not related to this article, we will cover those later. I wrote some scripts earlier to retrieve permissions from SQL 2000 and SQL 2005 (links below) however these scripts are no longer useful for SQL Server 2012 as it won’t return the new contained user and server level role permissions.

Script to Retrieve Security Information SQL Server 2005 and above
Script to Retrieve Security Information SQL Server 2000

New columns & DMV’s are added in SQL Server 2012 which will be helpful to retrieve those information’s so I’ve rewritten the scripts for you to work with SQL Server 2012. In addition to this I’ve added two more scripts which will return server level and database level permissions which is not included in SQL Server 2005 scripts

Works With

  • SQL Server 2012 (Denali) and above

Script to find server level roles assigned to Server level logins \ roles

SELECT a.name as Name,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,
ISNULL(SUSER_NAME(b.role_principal_id),'public') AS AssociatedServerRole
FROM sys.server_principals a LEFT JOIN sys.server_role_members b ON a.principal_id=b.member_principal_id
WHERE a.is_fixed_role <> 1 AND a.name NOT LIKE '##%'AND a.name <> 'public' ORDER BY Name, LoginType

Script to find Server level permissions assigned to Server level logins \ roles

SELECT a.name AS Name, a.type_desc AS LoginType,b.class_desc AS ClassDesc
,b.permission_name AS ServerLevelPermission,b.state_desc AS PermissionState
FROM sys.server_principals a JOIN sys.server_permissions b
ON a.principal_id = b.grantee_principal_id WHERE a.is_fixed_role <> 1
AND a.name NOT LIKE '##%' AND a.name NOT LIKE 'NT%' AND a.name <> 'public'  ORDER BY Name

Script to find all user database level roles assigned to database users \ roles

DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), Authentication_type VARCHAR(250),AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType, CASE a.authentication_type
WHEN 0 THEN ''No Authencication''
WHEN 1 THEN ''Uncontained User - Instance Level''
WHEN 2 THEN ''Contained User - Database Level''
WHEN 3 THEN ''Windows Login User'' END As AuthenticationType,
USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
RIGHT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid IS NOT NULL AND a.type NOT IN (''C'')
AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND a.name NOT LIKE ''NT%''
AND a.name NOT IN (''public'',''dbo'',''guest'')
AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY DBName'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName

Script to find all user database level permissions assigned to database users \ roles

DECLARE @Obj_sql VARCHAR(2000)
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250),Authentication_Type VARCHAR(250), Permission VARCHAR(500), PermissionState VARCHAR(200))
SET @Obj_sql='SELECT ''?'' AS DBName,a.name AS UserName,CASE a.authentication_type
WHEN 0 THEN ''No Authencication''
WHEN 1 THEN ''Uncontained User - Instance Level''
WHEN 2 THEN ''Contained User - Database Level''
WHEN 3 THEN ''Windows Login User'' END As AuthenticationType,
b.permission_name AS Permission,b.state_desc AS PermissionState
FROM ?.sys.database_principals a join
?.sys.database_permissions  b on a.principal_id=b.grantee_principal_id
WHERE a.name not in (''public'',''guest'',''dbo'') AND b.class <> 1
AND a.name NOT LIKE ''NT%'' AND ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'')'
INSERT @Obj_table
EXEC sp_msforeachdb @command1=@Obj_sql
SELECT * FROM @Obj_table ORDER BY DBName

Script to find all user database level object permissions assigned to database users \ roles

DECLARE @Obj_sql VARCHAR(2000)
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250),Authentication_Type VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200))
SET @Obj_sql='SELECT ''?'' AS DBName,U.name as username,  CASE S.authentication_type
WHEN 0 THEN ''No Authencication''
WHEN 1 THEN ''Uncontained User - Instance Level''
WHEN 2 THEN ''Contained User - Database Level''
WHEN 3 THEN ''Windows Login User'' END AS AuthenticationType,
O.name as object,  permission_name AS permission from ?.sys.database_permissions
JOIN ?.sys.sysusers U ON grantee_principal_id = uid
JOIN ?.sys.database_principals S ON s.principal_id=U.uid
JOIN ?.sys.sysobjects O ON major_id = id
WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'')'
INSERT @Obj_table
EXEC sp_msforeachdb @command1=@Obj_sql
SELECT * FROM @Obj_table ORDER BY DBName

Bug Fix

*) Logins in public roles are not displayed, same has been fixed now


Posted

in

by

Comments

One response to “Script to Retrieve Security Information – SQL Server 2012 (Denali) and above”

  1. SQL-Articles » Script to Retrieve Security Information – SQL Server 2012 (Denali) and above…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

Leave a Reply

Your email address will not be published. Required fields are marked *