Created
April 26, 2021 20:46
-
-
Save yehudamakarov/a520148cfb689ae6b8592031542df329 to your computer and use it in GitHub Desktop.
user permissions in sql
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE @login NVARCHAR(256), @user NVARCHAR(256); | |
SELECT @login = login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID; | |
SELECT @user = d.name | |
FROM sys.database_principals AS d | |
INNER JOIN sys.server_principals AS s | |
ON d.sid = s.sid | |
WHERE s.name = @login; | |
SELECT u.name, r.name | |
FROM sys.database_role_members AS m | |
INNER JOIN sys.database_principals AS r | |
ON m.role_principal_id = r.principal_id | |
INNER JOIN sys.database_principals AS u | |
ON u.principal_id = m.member_principal_id | |
WHERE u.name = @user; | |
SELECT class_desc, major_id, permission_name, state_desc | |
FROM sys.database_permissions | |
WHERE grantee_principal_id = USER_ID(@user); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment