1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
| SELECT SUSER_NAME(), SUSER_SNAME()
DECLARE @user_name SYSNAME = SUSER_NAME();
SELECT 'Logins and server roles'
SELECT
sp.name AS login_name,
sp2.name AS role_name
FROM sys.server_principals AS sp
LEFT JOIN sys.server_role_members srm
ON sp.principal_id = srm.member_principal_id
LEFT JOIN sys.server_principals AS sp2
ON sp2.principal_id = srm.role_principal_id
WHERE sp.type IN ('S', 'U', 'G')
AND sp.name = @user_name
SELECT 'Logins and permissions'
SELECT
sp.name AS login_name,
sp.type_desc AS [login_type],
spm.permission_name,
spm.state_desc
FROM sys.server_principals AS sp
INNER JOIN sys.server_permissions AS spm
ON sp.principal_id = spm.grantee_principal_id
WHERE sp.type IN ('S', 'U', 'G')
AND sp.name = @user_name
ORDER BY sp.name, spm.permission_name
SELECT 'Roles and users'
DECLARE @sql VARCHAR(MAX) = '
USE ?
SELECT
''?'' as database_name,
dp.name AS role_name,
dp2.name AS user_name,
pm.class_desc AS ObjectType,
CASE pm.class
WHEN 1 THEN o.name
WHEN 3 THEN s.name
WHEN 0 THEN DB_NAME()
END object_name,
pm.permission_name
FROM sys.database_principals AS dp
LEFT JOIN sys.database_role_members AS rlm
ON rlm.role_principal_id = dp.principal_id
LEFT JOIN sys.database_principals AS dp2
ON dp2.principal_id = rlm.member_principal_id
LEFT JOIN sys.database_permissions AS pm
ON dp.principal_id = pm.grantee_principal_id
LEFT JOIN sysobjects AS o
ON pm.major_id = o.id
LEFT JOIN sys.schemas AS s
ON pm.major_id = s.schema_id
WHERE dp.type = ''R''
AND dp2.name = ''' + @user_name + '''';
EXEC sp_MSforeachdb @sql; |
Partager