SQL USERS Completed Reference - Part1
SQL Server query to find list of all users in a databaseSQL Server query to find all permissions/access for all users in a database
SQL USERS Completed Reference -Part2
This Stored Procedure will provide a list of permissions that a user has either applied directly to the user account, or through roles under all databasesEg: EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
USE [master] ;
GO
IF EXISTS
(
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.uspGetPermissionsOfAllLogins_DBsOnColumns')
AND [type] in (N'P',N'PC')
)
BEGIN
DROP PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
END
GO
CREATE PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns
AS
SET NOCOUNT ON
;
BEGIN TRY
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#permission]')
)
DROP TABLE #permission
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#userroles_kk]')
)
DROP TABLE #userroles_kk
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[#rolemember_kk]')
)
DROP TABLE #rolemember_kk
;
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects
WHERE id = object_id(N'[tempdb].dbo.[##db_name]')
)
DROP TABLE ##db_name
;
DECLARE
@db_name VARCHAR(255)
,@sql_text VARCHAR(MAX)
;
SET @sql_text =
'CREATE TABLE ##db_name
(
LoginUserName VARCHAR(MAX)
,'
;
DECLARE cursDBs CURSOR FOR
SELECT [name]
FROM sys.databases
ORDER BY [name]
;
OPEN cursDBs
;
FETCH NEXT FROM cursDBs INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_text =
@sql_text + QUOTENAME(@db_name) + ' VARCHAR(MAX)
,'
FETCH NEXT FROM cursDBs INTO @db_name
END
CLOSE cursDBs
;
SET @sql_text =
@sql_text + 'IsSysAdminLogin CHAR(1)
,IsEmptyRow CHAR(1)
)'
--PRINT @sql_text
EXEC (@sql_text)
;
DEALLOCATE cursDBs
;
DECLARE
@RoleName VARCHAR(255)
,@UserName VARCHAR(255)
;
CREATE TABLE #permission
(
LoginUserName VARCHAR(255)
,databasename VARCHAR(255)
,[role] VARCHAR(255)
)
;
DECLARE cursSysSrvPrinName CURSOR FOR
SELECT [name]
FROM sys.server_principals
WHERE
[type] IN ( 'S', 'U', 'G' )
AND principal_id > 4
AND [name] NOT LIKE '##%'
ORDER BY [name]
;
OPEN cursSysSrvPrinName
;
FETCH NEXT FROM cursSysSrvPrinName INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #userroles_kk
(
databasename VARCHAR(255)
,[role] VARCHAR(255)
)
;
CREATE TABLE #rolemember_kk
(
dbrole VARCHAR(255)
,membername VARCHAR(255)
,membersid VARBINARY(2048)
)
;
DECLARE cursDatabases CURSOR FAST_FORWARD LOCAL FOR
SELECT [name]
FROM sys.databases
ORDER BY [name]
;
OPEN cursDatabases
;
DECLARE
@DBN VARCHAR(255)
,@sqlText NVARCHAR(4000)
;
FETCH NEXT FROM cursDatabases INTO @DBN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlText =
N'USE ' + QUOTENAME(@DBN) + ';
TRUNCATE TABLE #RoleMember_kk
INSERT INTO #RoleMember_kk
EXEC sp_helprolemember
INSERT INTO #UserRoles_kk
(DatabaseName,[Role])
SELECT db_name(),dbRole
FROM #RoleMember_kk
WHERE MemberName = ''' + @UserName + '''
'
--PRINT @sqlText ;
EXEC sp_executesql @sqlText ;
FETCH NEXT FROM cursDatabases INTO @DBN
END
CLOSE cursDatabases
;
DEALLOCATE cursDatabases
;
INSERT INTO #permission
SELECT
@UserName 'user'
,b.name
,u.[role]
FROM
sys.sysdatabases b
LEFT JOIN
#userroles_kk u
ON QUOTENAME(u.databasename) = QUOTENAME(b.name)
ORDER BY 1
;
DROP TABLE #userroles_kk
;
DROP TABLE #rolemember_kk
;
FETCH NEXT FROM cursSysSrvPrinName INTO @UserName
END
CLOSE cursSysSrvPrinName
;
DEALLOCATE cursSysSrvPrinName
;
TRUNCATE TABLE ##db_name
;
DECLARE
@d1 VARCHAR(MAX)
,@d2 VARCHAR(MAX)
,@d3 VARCHAR(MAX)
,@ss VARCHAR(MAX)
;
DECLARE cursPermisTable CURSOR FOR
SELECT * FROM #permission
ORDER BY 2 DESC
;
OPEN cursPermisTable
;
FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS
(
SELECT 1 FROM ##db_name WHERE LoginUserName = @d1
)
BEGIN
SET @ss =
'INSERT INTO ##db_name(LoginUserName) VALUES (''' + @d1 + ''')'
EXEC (@ss)
;
SET @ss =
'UPDATE ##db_name SET ' + @d2 + ' = ''' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''''
EXEC (@ss)
;
END
ELSE
BEGIN
DECLARE
@var NVARCHAR(MAX)
,@ParmDefinition NVARCHAR(MAX)
,@var1 NVARCHAR(MAX)
;
SET @var =
N'SELECT @var1 = ' + QUOTENAME(@d2) + ' FROM ##db_name WHERE LoginUserName = ''' + @d1 + ''''
;
SET @ParmDefinition =
N'@var1 NVARCHAR(600) OUTPUT '
;
EXECUTE Sp_executesql @var,@ParmDefinition,@var1 = @var1 OUTPUT
;
SET @var1 =
ISNULL(@var1, ' ')
;
SET @var =
' UPDATE ##db_name SET ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''' '
;
EXEC (@var)
;
END
FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
END
CLOSE cursPermisTable
;
DEALLOCATE cursPermisTable
;
UPDATE ##db_name SET
IsSysAdminLogin = 'Y'
FROM
##db_name TT
INNER JOIN
dbo.syslogins SL
ON TT.LoginUserName = SL.[name]
WHERE
SL.sysadmin = 1
;
DECLARE cursDNamesAsColumns CURSOR FAST_FORWARD LOCAL FOR
SELECT [name]
FROM tempdb.sys.columns
WHERE
OBJECT_ID = OBJECT_ID('tempdb..##db_name')
AND [name] NOT IN ('LoginUserName','IsEmptyRow')
ORDER BY [name]
;
OPEN cursDNamesAsColumns
;
DECLARE
@ColN VARCHAR(255)
,@tSQLText NVARCHAR(4000)
;
FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tSQLText =
N'UPDATE ##db_name SET
IsEmptyRow = ''N''
WHERE IsEmptyRow IS NULL
AND ' + QUOTENAME(@ColN) + ' IS NOT NULL
;
'
--PRINT @tSQLText ;
EXEC sp_executesql @tSQLText ;
FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
END
CLOSE cursDNamesAsColumns
;
DEALLOCATE cursDNamesAsColumns
;
UPDATE ##db_name SET
IsEmptyRow = 'Y'
WHERE IsEmptyRow IS NULL
;
UPDATE ##db_name SET
IsSysAdminLogin = 'N'
FROM
##db_name TT
INNER JOIN
dbo.syslogins SL
ON TT.LoginUserName = SL.[name]
WHERE
SL.sysadmin = 0
;
SELECT * FROM ##db_name
;
DROP TABLE ##db_name
;
DROP TABLE #permission
;
END TRY
BEGIN CATCH
DECLARE
@cursDBs_Status INT
,@cursSysSrvPrinName_Status INT
,@cursDatabases_Status INT
,@cursPermisTable_Status INT
,@cursDNamesAsColumns_Status INT
;
SELECT
@cursDBs_Status = CURSOR_STATUS('GLOBAL','cursDBs')
,@cursSysSrvPrinName_Status = CURSOR_STATUS('GLOBAL','cursSysSrvPrinName')
,@cursDatabases_Status = CURSOR_STATUS('GLOBAL','cursDatabases')
,@cursPermisTable_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
,@cursDNamesAsColumns_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
;
IF @cursDBs_Status > -2
BEGIN
CLOSE cursDBs ;
DEALLOCATE cursDBs ;
END
IF @cursSysSrvPrinName_Status > -2
BEGIN
CLOSE cursSysSrvPrinName ;
DEALLOCATE cursSysSrvPrinName ;
END
IF @cursDatabases_Status > -2
BEGIN
CLOSE cursDatabases ;
DEALLOCATE cursDatabases ;
END
IF @cursPermisTable_Status > -2
BEGIN
CLOSE cursPermisTable ;
DEALLOCATE cursPermisTable ;
END
IF @cursDNamesAsColumns_Status > -2
BEGIN
CLOSE cursDNamesAsColumns ;
DEALLOCATE cursDNamesAsColumns ;
END
SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ;
END CATCH
GO
/*
EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
*/
|
Here is the useful script to list all the SQL server Logins and roles. This script is useful in auditing
--#### LOGINS,USERS,ROLES (LUR) SCRIPT ####
--#### SCRIPT TESTED IN SQL SERVER 2000,2005,2008 AND 2008R2#### SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO -- CREATING A TEMP TABLE TO LOAD WITH DATABASE ROLES CREATE TABLE [TEMPDB].[DBO].[DB_ROLES]( [DBNAME] [SYSNAME] , [USERNAME] [SYSNAME] , [DB_OWNER] [VARCHAR](3) , [DB_ACCESSADMIN] [VARCHAR](3) , [DB_SECURITYADMIN] [VARCHAR](3) , [DB_DDLADMIN] [VARCHAR](3) , [DB_DATAREADER] [VARCHAR](3) , [DB_DATAWRITER] [VARCHAR](3) , [DB_DENYDATAREADER] [VARCHAR](3) , [DB_DENYDATAWRITER] [VARCHAR](3) , [DT_CREATE] [DATETIME] NOT NULL, [DT_UPDATE] [DATETIME] NOT NULL, [DT_REPORT] [DATETIME] NOT NULL CONSTRAINT [DF__DBROLES__CUR_DAT__3A179ED3] DEFAULT (GETDATE()) ) ON [PRIMARY] GO INSERT INTO [TEMPDB].[DBO].[DB_ROLES] EXEC SP_MSFOREACHDB ' SELECT ''?'' AS DBNAME, USERNAME, 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, CREATEDATE, UPDATEDATE, GETDATE() FROM ( SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME, B.CREATEDATE, B.UPDATEDATE FROM [?].DBO.SYSMEMBERS A JOIN [?].DBO.SYSUSERS B ON A.MEMBERUID = B.UID JOIN [?].DBO.SYSUSERS C ON A.GROUPUID = C.UID )S GROUP BY USERNAME, CREATEDATE, UPDATEDATE ORDER BY USERNAME' -- RETRIVE LOGINS, USERS AND ROLES TOGETHER SELECT SERVERPROPERTY('SERVERNAME') AS [SERVERNAME], B.NAME AS [LOGINNAME], CASE B.SYSADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SYSADMIN, CASE B.SECURITYADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SECURITYADMIN, CASE B.SETUPADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SETUPADMIN, CASE B.PROCESSADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS PROCESSADMIN, CASE B.DISKADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS DISKADMIN, CASE B.DBCREATOR WHEN '1' THEN 'YES' ELSE 'NO' END AS DBCREATOR, CASE B.BULKADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS BULKADMIN, B.DBNAME AS [DEFAULT_DBNAME], A.* INTO #LOGINS FROM [TEMPDB].[DBO].[DB_ROLES] A RIGHT JOIN MASTER..SYSLOGINS B ON A.USERNAME=B.NAME --WHERE B.ISNTUSER=1 --INCLUDE TO EXCLUDE THE SQL LOGINS SELECT * FROM #LOGINS ORDER BY [LOGINNAME] DROP TABLE [TEMPDB].[DBO].[DB_ROLES] DROP TABLE #LOGINS --------------SCRIPT END----------------------- |
Method:3 SQL Server List Login's Database
in the security audit of our server was to review the login's database role membership and server role membership.
There is a system stored procedure sp_helpsrvrolemember to list server role membership of login, but it is difficult to review the result of this procedure . Below script will give us the server role membership in more readable format.By copying the the result to Excel, it is more easy to review the server role membership of logins. More over system store procedure will not list the logins which have control server permission.
WITH CTE_Role (name,role,type_desc)
AS (SELECT PRN.name, srvrole.name AS [role] , Prn.Type_Desc FROM sys.server_role_members membership INNER JOIN (SELECT * FROM sys.server_principals WHERE type_desc='SERVER_ROLE') srvrole ON srvrole.Principal_id= membership.Role_principal_id RIGHT JOIN sys.server_principals PRN ON PRN.Principal_id= membership.member_principal_id WHERE Prn.Type_Desc NOT IN ('SERVER_ROLE') AND PRN.is_disabled =0 UNION ALL SELECT p.[name], 'ControlServer' ,p.type_desc AS loginType FROM sys.server_principals p JOIN sys.server_permissions Sp ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100 AND sp.[type] = 'CL' AND state = 'G' ) SELECT name, Type_Desc , CASE WHEN [public]=1 THEN 'Y' ELSE 'N' END AS 'Public', CASE WHEN [sysadmin] =1 THEN 'Y' ELSE 'N' END AS 'SysAdmin' , CASE WHEN [securityadmin] =1 THEN 'Y' ELSE 'N' END AS 'SecurityAdmin', CASE WHEN [serveradmin] =1 THEN 'Y' ELSE 'N' END AS 'ServerAdmin', CASE WHEN [setupadmin] =1 THEN 'Y' ELSE 'N' END AS 'SetupAdmin', CASE WHEN [processadmin] =1 THEN 'Y' ELSE 'N' END AS 'ProcessAdmin', CASE WHEN [diskadmin] =1 THEN 'Y' ELSE 'N' END AS 'DiskAdmin', CASE WHEN [dbcreator] =1 THEN 'Y' ELSE 'N' END AS 'DBCreator', CASE WHEN [bulkadmin] =1 THEN 'Y' ELSE 'N' END AS 'BulkAdmin' , CASE WHEN [ControlServer] =1 THEN 'Y' ELSE 'N' END AS 'ControlServer' FROM CTE_Role PIVOT( COUNT(role) FOR role IN ([public],[sysadmin],[securityadmin],[serveradmin],[setupadmin],[processadmin],[diskadmin],[dbcreator],[bulkadmin],[ControlServer]) ) AS pvt WHERE Type_Desc NOT IN ('SERVER_ROLE')ORDER BY name,type_desc ; go |
Method:4 SQL Server : Server Level Role Permission
The system stored procedure sp_helprolemember will help us to list the login's database role membership (system defined and user defined database role membership),but this will work only on database level. To get membership across all databases in an instance, this procedure has to run in each databases. The below script will list all users in all databases and its database role membership.
SET NOCOUNT ON
CREATE TABLE #DatabaseRoleMemberShip ( Username VARCHAR(100), Rolename VARCHAR(100), Databasename VARCHAR(100) )DECLARE @Cmd AS VARCHAR(MAX)DECLARE @PivotColumnHeaders VARCHAR(4000) SET @Cmd = 'USE [?] ;insert into #DatabaseRoleMemberShip select u.name,r.name,''?'' from sys.database_role_members RM inner join sys.database_principals U on U.principal_id=RM.member_principal_id inner join sys.database_principals R on R.principal_id=RM.role_principal_id where u.type<>''R'''EXEC sp_MSforeachdb @command1=@cmd SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + CAST(rolename AS VARCHAR(MAX)) + ']','[' + CAST(rolename AS VARCHAR(MAX))+ ']' ) FROM (SELECT DISTINCT rolename FROM #DatabaseRoleMemberShip )a ORDER BY rolename ASC SET @Cmd = 'select databasename,username,'+@PivotColumnHeaders+' from ( select * from #DatabaseRoleMemberShip) as p pivot ( count(rolename ) for rolename in ('+@PivotColumnHeaders+') )as pvt'EXECUTE(@Cmd ) DROP TABLE #DatabaseRoleMemberShip go |
Method:5 SQL Server : Find Culprit CONTROL SERVER permission
Today morning I was working on one of the server to complete the regular documentation of security audit with details of logins with sysadmin rights and their access to various database. Later I have realized that, one login has access to all database but it is not captured by my auditing script. Here comes the culprit , the CONTROL SERVER rights.
By default CONTROL SERVER rights is equivalent to sysadmin permission except the fact that logins with CONTROL SERVER rights will honor the explicitly denied server level permission where the members of sysadmin server role bypass the explicitly denied server level permission. Also note that logins with control server permission will have implicit access to the databases like the sysadmin members and database owners.
Logins with control server rights will not have mapping entry in the sys.database_principals but it will have access to all databases. The worst part is, logins with CONROL SERVER permission are not easy to find out unless you prepare explicit query. It is not listed in the UI of SSMS or there is no system procedure like sp_helpsrvrolemember to list the logins with CONTROL SERVER right.
Let us walk through a sample script. Create two logins using the below script
CREATE login SysadminLogin WITH password ='password123~'
GO CREATE login controlserverlogin WITH password ='password123~' GO EXEC sp_addsrvrolemember 'SysadminLogin','sysadmin' GO GRANT control server TO controlserverlogin Now log in to the server using the controlserverlogin and you can access all the databases and perform any actions. Let us see what will happen on explicitly denying the server level permission. DENY VIEW ANY DATABASE TO controlserverlogin GO DENY VIEW ANY DATABASE TO sysadminlogin GO |
Now log in to the server using both the login. You can notice that, in the session that connected with the controlserverlogin will list only Master and Tempdb databases while the session connected with sysadminlogin will list all available databases.
The other potential issue with logins having control server right is , they can add them self to the sysadmin server role or can create a new login with membership to the sysadmin server role. Fortunately it is not possible to do it in straight forward steps. Let us see how it will work .Connect to the server using the controlserverlogin and execute the below scripts
/* Fortunately this will fail */
EXEC sp_addsrvrolemember 'controlserverlogin','sysadmin'; GO /* Unfortunately this will work even if sa account is disabled*/ EXECUTE AS LOGIN = 'sa'; GO EXEC sp_addsrvrolemember 'controlserverlogin','sysadmin'; GO REVERT; |
Now the controlserverlogin has sysadmin role membership and you can see all available databases.
Below script list the logins with sysadmin role membership and control server permission.
/* List login with membership to Sysadmin server role and Control Server right*/
SELECT p.[name] [Login], 'sysadmin Role Member' [Access] ,p.type_desc AS loginTypeFROM sys.server_principals p JOIN sys.server_role_members RM ON p.principal_id = rm.member_principal_id JOIN sys.server_principals rp ON rm.role_principal_id = rp.principal_id WHERE rp.NAME = 'sysadmin' UNION ALL SELECT p.[name], 'Control Server Right' ,p.type_desc AS loginTypeFROM sys.server_principals p JOIN sys.server_permissions Sp ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100 AND sp.[type] = 'CL' AND state = 'G' GO |
Below script will help to list the explicitly denied server level permission for logins which have control server permission.
/* List Explicitly denied permission for the login that has control server permission*/
SELECT CSL.*,sp.permission_name [Explicitly Denied Permission] FROM sys.server_permissions SP INNER JOIN (SELECT p.principal_id ,p.[name], p.type_desc AS loginTypeFROM sys.server_principals p JOIN sys.server_permissions Sp ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100 AND sp.[type] = 'CL' AND state = 'G' ) CSL ON CSL.principal_id = sp.grantee_principal_id WHERE state ='D' |
Method:6 SQL Server : Script to List All SQL Server Database Users and Their Access Rights into a Flat File
I have a task to produce a hard copy of all database users and each access rights of all SQL Server servers. I would like to run this once a week in batch. Is this achievable?
You can do this in many ways. If you are creating a BAT file, you can save the following code and then use your BAT to execute it. It has two option commented in the code to use a single database or all databases on the target server you enter in the BAT. I have included a sample of the batch at the bottom:
You can do this in many ways. If you are creating a BAT file, you can save the following code and then use your BAT to execute it. It has two option commented in the code to use a single database or all databases on the target server you enter in the BAT. I have included a sample of the batch at the bottom:
--Example of batch file to execute
--sqlcmd -S YOURTARGETSERVERHERE -E -i "C:\YOURPATHTOSQLSCRIPT\USERPERMISSIONS.SQL&qu ot; -s , -o "C:\YOURPATHHERE\UserPermisions.CSV" |
Save the following files as "USERPERMISSIONS.SQL"
else run directly.
set nocount on
declare @permission table ( Database_Name sysname, User_Role_Name sysname, Account_Type nvarchar(60), Action_Type nvarchar(128), Permission nvarchar(60), ObjectName sysname null, Object_Type nvarchar(60) ) declare @dbs table (dbname sysname) declare @Next sysname insert into @dbs --Use this for all databases select name from sys.databases order by name --Uncomment and use this for a single DB --select name from 'YOURDBNAMEHERE' as dbname select top 1 @Next = dbname from @dbs while (@@rowcount<>0) begin insert into @permission exec('use [' + @Next + '] declare @objects table (obj_id int, obj_type char(2)) insert into @objects select id, xtype from master.sys.sysobjects insert into @objects select object_id, type from sys.objects SELECT ''' + @Next + ''', a.name as ''User or Role Name'', a.type_desc as ''Account Type'', d.permission_name as ''Type of Permission'', d.state_desc as ''State of Permission'', OBJECT_SCHEMA_NAME(d.major_id) + ''.'' + object_name(d.major_id) as ''Object Name'', case e.obj_type when ''AF'' then ''Aggregate function (CLR)'' when ''C'' then ''CHECK constraint'' when ''D'' then ''DEFAULT (constraint or stand-alone)'' when ''F'' then ''FOREIGN KEY constraint'' when ''PK'' then ''PRIMARY KEY constraint'' when ''P'' then ''SQL stored procedure'' when ''PC'' then ''Assembly (CLR) stored procedure'' when ''FN'' then ''SQL scalar function'' when ''FS'' then ''Assembly (CLR) scalar function'' when ''FT'' then ''Assembly (CLR) table-valued function'' when ''R'' then ''Rule (old-style, stand-alone)'' when ''RF'' then ''Replication-filter-procedure'' when ''S'' then ''System base table'' when ''SN'' then ''Synonym'' when ''SQ'' then ''Service queue'' when ''TA'' then ''Assembly (CLR) DML trigger'' when ''TR'' then ''SQL DML trigger'' when ''IF'' then ''SQL inline table-valued function'' when ''TF'' then ''SQL table-valued-function'' when ''U'' then ''Table (user-defined)'' when ''UQ'' then ''UNIQUE constraint'' when ''V'' then ''View'' when ''X'' then ''Extended stored procedure'' when ''IT'' then ''Internal table'' end as ''Object Type'' FROM [' + @Next + '].sys.database_principals a left join [' + @Next + '].sys.database_permissions d on a.principal_id = d.grantee_principal_id left join @objects e on d.major_id = e.obj_id order by a.name, d.class_desc') delete @dbs where dbname = @Next select top 1 @Next = dbname from @dbs end set nocount off select * from @permission |
Method:7 SQL Server : Object rights
create table ##ObjRights(
[ServerName] nvarchar(128), [DatabaseName] nvarchar(128), [Owner] nvarchar(128), [Object] nvarchar(128), [Grantee] nvarchar(128), [Grantor] nvarchar(128), [ProtectType] nvarchar(20), [Action] nvarchar(50), [Column] nvarchar(4000)); declare dbs cursor for select name from master.dbo.sysdatabases; declare @db nvarchar(128) ,@sql varchar(500); open dbs; fetch dbs into @db; while @@fetch_status=0 begin set @sql = 'use '+@db+'; insert ##ObjRights ([Owner], [Object], [Grantee], [Grantor], [ProtectType], [Action], [Column]) exec sp_helprotect; update ##ObjRights set DatabaseName='''+@db+''' where DatabaseName is null;' exec(@sql); fetch dbs into @db; end close dbs; deallocate dbs; update ##ObjRights set ServerName= case when convert(nvarchar, serverproperty('InstanceName')) is null then convert(nvarchar, serverproperty('MachineName')) else convert(nvarchar, serverproperty('MachineName'))+N'/'+convert(nvarchar, serverproperty('InstanceName')) end; select * from ##ObjRights; drop table ##ObjRights; go |