Usage : EXEC USP_EffectivePermissions 'username'
Install in master db to use from any db on the server.
CREATE PROC USP_EffectivePermissions
@User VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON
IF OBJECT_ID('tempdb..##ServerLogins') IS NOT NULL
BEGIN
DROP TABLE ##SERVERLOGINS
END
IF OBJECT_ID('tempdb..##DBUsers') IS NOT NULL
BEGIN
DROP TABLE ##DBUSERS
END
IF OBJECT_ID('tempdb..##DBList') IS NOT NULL
BEGIN
DROP TABLE ##DBLIST
END
IF OBJECT_ID('tempdb..##DBObjPermissions') IS NOT NULL
BEGIN
DROP TABLE ##DBOBJPERMISSIONS
END
IF OBJECT_ID('tempdb..##CrossJoinMultiplier') IS NOT NULL
BEGIN
DROP TABLE ##CROSSJOINMULTIPLIER
END
IF OBJECT_ID('tempdb..##Results') IS NOT NULL
BEGIN
DROP TABLE ##RESULTS
END
DECLARE @DBName VARCHAR(128);
DECLARE @SQLCmd VARCHAR(2000);
DECLARE @NumberOfDBs INT;
-- Get the SQL Server logins
-- Create login table
CREATE TABLE ##SERVERLOGINS ([SID] VARBINARY(85) NULL,[LOGIN_NAME] VARCHAR(128) NULL);
-- Populate login table
INSERT INTO ##SERVERLOGINS
SELECT SID,CAST(LOGINNAME AS VARCHAR(128)) AS [LOGIN_NAME]
FROM MASTER.DBO.SYSLOGINS
WHERE LOGINNAME = @User;
-- Create list of databases
CREATE TABLE ##DBLIST (
[DBNAME] VARCHAR(128))
-- perform for all dbs on server
INSERT INTO ##DBLIST
SELECT NAME
FROM MASTER..SYSDATABASES
WHERE DBID > 4
ORDER BY NAME;
SELECT @NumberOfDBs = COUNT(* )
FROM ##DBLIST
-- Create the output table for the Database User ID's
CREATE TABLE ##DBUSERS (
[DATABASE_USER_ID] VARCHAR(128),
[SERVER_LOGIN] VARCHAR(128),
[DATABASE_ROLE] VARCHAR(128),
[DATABASE_NAME] VARCHAR(128));
-- Create the output table for Object Level Permissions
CREATE TABLE ##DBOBJPERMISSIONS (
[DATABASE_NAME] VARCHAR(128),
[GRANTOR] VARCHAR(128),
[GRANTEE] VARCHAR(128),
[OBJTYPE] VARCHAR(128),
[OBJECTNAME] VARCHAR(128),
[PERMISSION_LEVEL] VARCHAR(128),
[PERMISSION] VARCHAR(128));
-- Declare a cursor to loop through all the databases on the server
DECLARE CSRDB CURSOR FOR
SELECT DBNAME
FROM ##DBLIST
ORDER BY DBNAME
-- Open the cursor and get the first database name
OPEN CSRDB
FETCH NEXT FROM CSRDB
INTO @DBName
-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- populate ##DBUsers table for current db
SELECT @SQLCmd = 'INSERT ##DBUsers '
+ ' SELECT CAST(su.[name] AS VARCHAR(128)) AS [database_user_id], '
+ ' CAST(COALESCE (u.[login_name], ''* Orphaned *'') AS VARCHAR(128))AS [server_login], '
+ ' CAST(COALESCE (sug.name, ''Public'') AS VARCHAR(128)) AS [database_role],'
+ ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name]'
+ ' FROM [' + @DBName + '].[dbo].[sysusers] su'
+ ' LEFT OUTER JOIN ##ServerLogins u' + ' ON su.sid = u.sid'
+ ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm '
+ ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug '
+ ' ON sm.groupuid = sug.uid)'
+ ' ON su.uid = sm.memberuid '
+ ' WHERE su.hasdbaccess = 1'
+ ' AND SU.name = ''' + @User + ''''
-- uncomment to debug
-- PRINT @SQLCmd
EXEC(@SQLCmd)
-- populate ##DBObjPermissions table for current db
SELECT @SQLCmd = 'use [' + @DBName + '];'
+ 'insert into ##DBObjPermissions '
+ ' select '
+ ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name],'
+ ' user_name(sec.grantor) as grantor, '
+ ' user_name(sec.uid) as grantee, '
+ ' case obj.type '
+ ' when ''C'' then ''Check constraint'' '
+ ' when ''D'' then ''Default (constraint or stand-alone)'' '
+ ' when ''F'' then ''Foreign Key'' '
+ ' when ''PK'' then ''Primary Key'' '
+ ' when ''P'' then ''Stored Procedure'' '
+ ' when ''FN'' then ''Function (Scalar)'' '
+ ' when ''IF'' then ''Function (Inline)'' '
+ ' when ''R'' then ''Rule (old-style, stand-alone)'' '
+ ' when ''RF'' then ''Replication-filter-procedure'' '
+ ' when ''S'' then ''System base table'' '
+ ' when ''TA'' then ''Assembly (CLR) DML trigger'' '
+ ' when ''TF'' then ''Function (TableValued)'' '
+ ' when ''U'' then ''Table'' '
+ ' when ''UQ'' then ''Unique constraint'' '
+ ' when ''V'' then ''View'' '
+ ' when ''X'' then ''Extended stored procedure'' '
+ ' end as objtype, '
+ ' stbl.name + ''.'' + obj.name as objectname, '
+ ' protecttype.name permission_level, ' + ' action.name as permission '
+ ' from ' + '[' + @DBName + '].dbo.sysobjects as obj '
+ ' inner join [' + @DBName + '].dbo.sysusers as stbl on stbl.uid = obj.uid '
+ ' and stbl.name = ''' + @User + ''''
+ ' inner join ##DBUsers on ##DBUsers.[database_user_id] COLLATE SQL_Latin1_General_CP1_CS_AS = stbl.name COLLATE SQL_Latin1_General_CP1_CS_AS'
+ ' inner join [' + @DBName + '].dbo.sysprotects as sec on sec.id = obj.id '
+ ' inner join master.dbo.spt_values as action on sec.action = action.number and action.type = ''t'' '
+ ' inner join master.dbo.spt_values as protecttype on sec.protecttype = protecttype.number and protecttype.type = ''t'' '
+ ' where objectpropertyex(obj.id,''ismsshipped'') = 0 '
-- uncomment to debug
-- PRINT @SQLCmd
EXEC(@SQLCmd)
-- Get the next database name
FETCH NEXT FROM CSRDB
INTO @DBName
-- End of the cursor loop
END
-- Close and deallocate the CURSOR
CLOSE CSRDB
DEALLOCATE CSRDB
CREATE TABLE ##CROSSJOINMULTIPLIER (
USERNAME VARCHAR(128) NULL);
IF @NumberOfDBs = 1
INSERT INTO ##CROSSJOINMULTIPLIER
SELECT [SERVER_LOGIN]
FROM ##DBUSERS -- 1 db, multiply section headers by users
ELSE
INSERT INTO ##CROSSJOINMULTIPLIER
SELECT TOP 1 'dummy'
FROM SYSOBJECTS -- multiple dbs,
CREATE TABLE ##RESULTS (
[HEADER_ROW] INT,[DATABASE_NAME] VARCHAR(128),[SORT_ORDER] INT,[GRANTOR] VARCHAR(128),[GRANTEE] VARCHAR(128),[OBJTYPE] VARCHAR(128),[OBJECTNAME] VARCHAR(128),[USERGRANTEE] VARCHAR(128),[PERMISSION_LEVEL] VARCHAR(128),[PERMISSION] VARCHAR(128));
TRUNCATE TABLE ##RESULTS
--data for logins
INSERT INTO ##RESULTS
SELECT
0 AS HEADER_ROW,
CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DATABASE_NAME AS DATABASE_NAME,
2 AS SORT_ORDER,
'' AS GRANTOR,
'' AS GRANTEE,
MAX(CASE [DATABASE_ROLE] WHEN 'db_datareader' THEN 'db_datareader, ' ELSE '' END) +
MAX(CASE [DATABASE_ROLE] WHEN 'db_accessadmin' THEN 'db_accessadmin, ' ELSE '' END) +
MAX(CASE [DATABASE_ROLE] WHEN 'db_backupoperator' THEN 'db_backupoperator, ' ELSE '' END) +
MAX(CASE [DATABASE_ROLE] WHEN 'db_datawriter' THEN 'db_datawriter, ' ELSE '' END) +
MAX(CASE [DATABASE_ROLE] WHEN 'db_ddladmin' THEN 'db_ddladmin, ' ELSE '' END) +
MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatareader' THEN 'db_denydatareader, ' ELSE '' END) +
MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatawriter' THEN 'db_denydatawriter, ' ELSE '' END) +
MAX(CASE [DATABASE_ROLE] WHEN 'db_owner' THEN 'db_owner, ' ELSE '' END) +
MAX(CASE [DATABASE_ROLE] WHEN 'db_securityadmin' THEN 'db_securityadmin, ' ELSE '' END) +
MAX(CASE [DATABASE_ROLE] WHEN 'public' THEN 'public, ' ELSE '' END) AS ROLE_OBJECTTYPE,
'database user' AS OBJ_NAME,
CASE [SERVER_LOGIN] WHEN [DATABASE_USER_ID] THEN [DATABASE_USER_ID] ELSE [DATABASE_USER_ID] + ' (' + [SERVER_LOGIN] + ')' END AS USER_GRANTEE,
'' AS PERMISSION_LEVEL,
'' AS PERMISSION
FROM
##DBUSERS
GROUP BY
[DATABASE_NAME],
[DATABASE_USER_ID],
[SERVER_LOGIN]
-- data for objects
INSERT INTO ##RESULTS
SELECT 0 AS HEADER_ROW,
CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DATABASE_NAME AS DATABASE_NAME,
4 AS SORT_ORDER,
GRANTOR,
GRANTEE,
[OBJTYPE] AS ROLE_OBJECTTYPE,
[OBJECTNAME] AS OBJ_NAME,
[GRANTEE] AS USER_GRANTEE,
[PERMISSION_LEVEL] AS PERMISSION_LEVEL,
[PERMISSION] AS PERMISSION
FROM ##DBOBJPERMISSIONS
INSERT INTO ##RESULTS
SELECT 1 AS HEADER_ROW,
CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DBNAME AS DATABASE_NAME,
1 AS SORT_ORDER,
'' AS GRANTOR,
'' AS GRANTEE,
'Role' AS ROLE_OBJECTTYPE,
'Object' AS OBJ_NAME,
'User' AS USER_GRANTEE,
' ' AS PERMISSION_LEVEL,
' ' AS PERMISSION
FROM ##DBLIST
INNER JOIN ##RESULTS
ON ##RESULTS.DATABASE_NAME = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + ##DBLIST.DBNAME
AND ##RESULTS.SORT_ORDER = 2
-- header rows for objects
INSERT INTO ##RESULTS
SELECT 1 AS HEADER_ROW,
CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DBNAME AS DATABASE_NAME,
3 AS SORT_ORDER,
'' AS GRANTOR,
'' AS GRANTEE,
'Object Type' AS ROLE_OBJECTTYPE,
'Object' AS OBJ_NAME,
'Grantee' AS USER_GRANTEE,
'Permission Level' AS PERMISSION_LEVEL,
'Permission' AS PERMISSION
FROM ##DBLIST
INNER JOIN ##RESULTS
ON ##RESULTS.DATABASE_NAME = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + ##DBLIST.DBNAME
AND ##RESULTS.SORT_ORDER = 4
SELECT
DATABASE_NAME,
GRANTOR,
GRANTEE,
OBJTYPE,
OBJECTNAME,
USERGRANTEE,
PERMISSION_LEVEL,
PERMISSION
FROM
##RESULTS
ORDER BY
DATABASE_NAME,
SORT_ORDER
END
go
No comments:
Post a Comment