How To Check User Privileges in SQL Server

dbtut April 15, 2019 MSSQL

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)

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

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)”

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'))

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.

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

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.

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

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.

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

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

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

About dbtut

2 comments

Leave a Reply Cancel reply