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
Leave a Reply