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,
SUSER_NAME(b.role_principal_id) AS AssociatedServerRole
FROM sys.server_principals a 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 NOT LIKE 'NT%'
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
No comments:
Post a Comment