Created
August 14, 2025 16:29
-
-
Save ScottMonolith/fbf364a65bb55605a66fb17738af4005 to your computer and use it in GitHub Desktop.
Azure 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
| Find Permissions | |
| SELECT | |
| u.name as UserName, | |
| u.type_desc as UserType, | |
| r.name as RoleName | |
| FROM | |
| sys.database_principals as u | |
| LEFT JOIN | |
| sys.database_role_members as rm on rm.member_principal_id= u.principal_id | |
| LEFT JOIN | |
| sys.database_principals as r on r.principal_id = rm.role_principal_id | |
| WHERE | |
| u.type NOT IN ('R', 'G') | |
| ORDER BY UserName, RoleName | |
| Add Permissions | |
| EXEC sp_addrolemember N'db_datawriter', N'Process Engineers DB Access' | |
| EXEC sp_addrolemember N'db_datareader', N'Process Engineers DB Access' | |
| EXEC sp_addrolemember N'CreateObjects', N'Process Engineers DB Access' | |
| Create new role that can create any object | |
| CREATE ROLE CreateObjects; | |
| GRANT CREATE TABLE TO | |
| CreateObjects; | |
| GRANT CREATE VIEW TO | |
| CreateObjects; | |
| GRANT CREATE FUNCTION TO | |
| CreateObjects; | |
| GRANT CREATE PROCEDURE TO | |
| CreateObjects; | |
| GRANT ALTER ANY SCHEMA TO | |
| CreateObjects; | |
| GRANT EXECUTE TO CreateObjects; | |
| Find what permissions a specific role has (change pr.name with role name, or remove where clause to see all permissions) | |
| SELECT | |
| dp.state_desc AS PermissionState, | |
| dp.permission_name AS PermissionName, | |
| dp.class_desc AS ObjectType, | |
| OBJECT_NAME(dp.major_id) AS ObjectName, | |
| dp.grantee_principal_id, | |
| pr.name AS PrincipalName | |
| FROM | |
| sys.database_permissions dp | |
| JOIN | |
| sys.database_principals pr | |
| ON | |
| dp.grantee_principal_id = pr.principal_id | |
| WHERE | |
| pr.name = 'CreateObjects'; | |
| Show what users have which role(s) | |
| SELECT dp.name as rolename, | |
| dp.type_desc as RoleType | |
| FROM SYS.database_principals as dp | |
| where dp.type_desc like '%ROLE%' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment