--for sql logins
SELECT * FROM sys.sql_logins;
SELECT * FROM sys.sysusers;
SELECT * FROM sys.database_principals
--- to connect master sql logins to the users in the user databases --- run this in user database
select l.name as [login name],u.name as [user name] from sysusers u inner join sys.sql_logins l on u.sid=l.sid
-- For AAD logins:
SELECT * FROM sys.server_principals
SELECT * FROM sys.database_principals
-- To retrive role and it's granted members:
SELECT roles.principal_id AS RolePrincipalID
, roles.name AS RolePrincipalName
, database_role_members.member_principal_id AS MemberPrincipalID
, members.name AS MemberPrincipalName
FROM sys.database_role_members AS database_role_members
JOIN sys.database_principals AS roles
ON database_role_members.role_principal_id = roles.principal_id
JOIN sys.database_principals AS members
ON database_role_members.member_principal_id = members.principal_id
order by 2;
GO
-- display role and members
--select * FROM sys.database_role_members
--select * FROM sys.database_principals order by name
SELECT DP1.principal_id AS DBRolePrincipalID,
DP1.name AS DatabaseRoleName,
DRM.member_principal_id AS MemberDBPrincipalID,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;
-- retrive database object level permissions granted
--select * from sys.database_permissions
SELECT DISTINCT pr.principal_id, pr.name AS [UserName], pr.type_desc AS [User_or_Role], pr.authentication_type_desc AS [Auth_Type], pe.state_desc,
pe.permission_name, pe.class_desc, o.[name] AS 'Object'
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
LEFT JOIN sys.objects AS o on (o.object_id = pe.major_id)
order by 3,2
Comments
Post a Comment