Due to the principle of “minimum authority for maximum security”, we should regularly check which user is authorized on the sql server, and revoke the unnecessary privileges from the users. In this article I will share scripts related to the following levels of authority.
- Server Level Authorized Users(Logins)
- Database LevelAuthorized Users(Logins)
- Schema Level Authorized Users(Logins)
- Object Level Authorized Users(Logins)
Server Level Authorized Users(Logins)
|
1 2 3 4 |
SELECT sp.name Server_Role, sp2.name Login_Name FROM sys.server_role_members srm JOIN sys.server_principals sp on sp.principal_id=srm.role_principal_id JOIN sys.server_principals sp2 on sp2.principal_id=srm.member_principal_id |
Database Level Authorized Users(Logins)
You can use below script to see database level authorized users. But it shows orphaned users too. So first you should check if you have orphaned users in your databases. Check the article; “Tip: “sp_validatelogins” and “sp_change_users_login”(Orphaned Users in SQL Server)”
|
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 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 |
Declare @database nvarchar(128)=null, @user varchar(20)=null, @dbo char(1)=null, @access char(1)=null, @security char(1)=null, @ddl char(1)=null, @datareader char(1)=null, @datawriter char(1)=null, @denyread char(1)=null, @denywrite char(1)=null declare @dbname varchar(200) declare @mSql1 varchar(8000) CREATE TABLE #DBROLES ( DBName sysname not null, UserName sysname not null, db_owner varchar(3) not null, db_accessadmin varchar(3) not null, db_securityadmin varchar(3) not null, db_ddladmin varchar(3) not null, db_datareader varchar(3) not null, db_datawriter varchar(3) not null, db_denydatareader varchar(3) not null, db_denydatawriter varchar(3) not null, Cur_Date datetime not null default getdate() ) DECLARE DBName_Cursor CURSOR FOR select name from master.dbo.sysdatabases where name not in ('mssecurity','tempdb') Order by name OPEN DBName_Cursor FETCH NEXT FROM DBName_Cursor INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN Set @mSQL1 = ' Insert into #DBROLES ( DBName, UserName, db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter ) SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ ' Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner, Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin , Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin, Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin, Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader, Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter, Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader, Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter from ( select b.name as USERName, c.name as RoleName from ' + @dbName+'.dbo.sysmembers a '+char(13)+ ' join '+ @dbName+'.dbo.sysusers b '+char(13)+ ' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c on a.groupuid = c.uid )s Group by USERName order by UserName' --Print @mSql1 Execute (@mSql1) FETCH NEXT FROM DBName_Cursor INTO @dbname END CLOSE DBName_Cursor DEALLOCATE DBName_Cursor Select * from #DBRoles where ((@database is null) OR (DBName LIKE '%'+@database+'%')) AND ((@user is null) OR (UserName LIKE '%'+@user+'%')) AND ((@dbo is null) OR (db_owner = 'Yes')) AND ((@access is null) OR (db_accessadmin = 'Yes')) AND ((@security is null) OR (db_securityadmin = 'Yes')) AND ((@ddl is null) OR (db_ddladmin = 'Yes')) AND ((@datareader is null) OR (db_datareader = 'Yes')) AND ((@datawriter is null) OR (db_datawriter = 'Yes')) AND ((@denyread is null) OR (db_denydatareader = 'Yes')) AND ((@denywrite is null) OR (db_denydatawriter = 'Yes')) |
Schema Level Authorized Users(Logins)
If you are using the always on availability group, it returns results for primary databases and also standalone databases.
|
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 |
DECLARE @name NVARCHAR(MAX),@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX); DECLARE Crs CURSOR FOR SELECT name FROM sys.sysdatabases where dbid>4 and name not in( SELECT DB_NAME(dr_state.database_id) as name FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id ) JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id) JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id and dr_state.replica_id = ar_state.replica_id where ar_state.role_desc='SECONDARY' AND ar_state.is_local=1 ) OPEN Crs FETCH NEXT FROM Crs INTO @Name WHILE @@FETCH_STATUS =0 BEGIN Select @Sql = 'Use ' + @name + '; if exists(select 1 FROM sys.database_permissions WHERE class_desc = ''SCHEMA'') BEGIN SELECT '''+@name+''' AS [DBName],SCHEMA_NAME(major_id) [SchemaName] , USER_NAME(grantee_principal_id) [Login Name] , permission_name [Privilege] FROM sys.database_permissions WHERE class_desc = ''SCHEMA'' ORDER BY major_id, grantee_principal_id, permission_name END ' print @Sql Exec sp_executesql @Sql print @sql FETCH NEXT FROM Crs INTO @Name END CLOSE Crs DEALLOCATE Crs |
Object Level Authorized Users(Logins)
You can see these privileges for the single database with the following script.
|
1 2 3 4 5 |
SELECT permission_name AS Privilege, type_desc [Object Type], U.name [Login Name], OBJECT_NAME(major_id) [Object Name] from sys.database_permissions dp JOIN sys.tables tbl ON dp.major_id = tbl.object_id JOIN sysusers u ON u.uid = dp.grantee_principal_id |
If you are using the always on availability group, you can use the below script for primary databases of availabilityg groups and also standalone databases on the instance.
|
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 |
DECLARE @name NVARCHAR(MAX),@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX); DECLARE Crs CURSOR FOR SELECT name FROM sys.sysdatabases where dbid>4 and name not in( SELECT DB_NAME(dr_state.database_id) as name FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id ) JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id) JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id and dr_state.replica_id = ar_state.replica_id where ar_state.role_desc='SECONDARY' AND ar_state.is_local=1 ) OPEN Crs FETCH NEXT FROM Crs INTO @Name WHILE @@FETCH_STATUS =0 BEGIN Select @Sql = 'Use ' + @name + '; if exists(SELECT 1 from sys.database_permissions dp JOIN sys.tables tbl ON dp.major_id = tbl.object_id JOIN sysusers u ON u.uid = dp.grantee_principal_id) BEGIN SELECT '''+@name+''' DBName,permission_name AS Privilege, type_desc [Object Type], U.name [Login Name], OBJECT_NAME(major_id) [Object Name] from sys.database_permissions dp JOIN sys.tables tbl ON dp.major_id = tbl.object_id JOIN sysusers u ON u.uid = dp.grantee_principal_id END ' Exec sp_executesql @Sql FETCH NEXT FROM Crs INTO @Name END CLOSE Crs DEALLOCATE Crs |
