I need to run a report to return all the users have access rights but having a problem to return those users in Server roles.
Ex: if JSmith in ServerAdmin and SetupAdmin Roles, he should return 2 roles but I it return only 1 role serveradmin role.
I know why because of the UPDATE statement below but can't think of the way to fix it.
Any help would greatly appreciate. Please see the desire output below.
I am using SQL 2012 and SQL 2008.
-- stop Issues.
-- This section update if a user is in Server Role.
UPDATE a
SET server_role = b.ServerRole
FROM @FinalResult AS a
JOIN dbo.#ServerRole AS b
ON ( RTRIM(a.iuser_name) = RTRIM(b.UserName) );
-- dbo.#ServerRole
ServerRole UserName TypeDesc
------------------ ---------------- --------------
serveradmin INT\JSmith WINDOWS_LOGIN
setupadmin INT\JSmith WINDOWS_LOGIN
-- Desire output.
Servername DatabaseName UserName TypeDesc DatabaseRole ServerRole
---------------- ---------------- --------------- ----------------- ----------------- -----------
CABISETestABC Prod1 INT\JSmith WINDOWS_USER db_datareader serveradmin
CABISETestABC Prod1 INT\JSmith WINDOWS_USER db_datawriter setupadmin
-----------------------------------------------------------------------------------------------------
USE [msdb];
GO
IF OBJECT_ID('dbo.usp_AdminReturnUserAccessReport', 'p') IS NOT NULL
DROP PROCedure dbo.usp_AdminReturnUserAccessReport
GO
CREATE PROCedure dbo.usp_AdminReturnUserAccessReport
(
@DBName VARCHAR(60) = NULL
,@UserName VARCHAR(25) = NULL
)
AS
/*****************************************************************************************
** Description: This sp report all users belong to certain role(s). See output
** below. This work on SQL Server 2012 too. Tested 01/14/2014.
**
** Written date: 01/26/2009
**
**
** Modifications:
** ------------+---------------+-------------------------------------------------------
** Date: | Author: | Reasons:
** xx/xx/xxxx | xxxxxxxxxx | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
**
** ---------- -------------- --------------------------------------------------------
******************************************************************************************/
SET NOCOUNT ON;
/*
PRINT '/********************************************'
PRINT 'Server Name: ' + @@SERVERNAME
PRINT 'Today is: ' + CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 126) + RIGHT(CONVERT(VARCHAR(35), GETDATE(), 109), 14)
PRINT '*********************************************/'
PRINT ' '
PRINT ' '
--------------------------------------------------------
*/
DECLARE @FinalResult TABLE
(
seq_num INT NOT NULL
,database_name VARCHAR(40) NULL
,iuser_name VARCHAR(40) NULL
,principal_type_desc VARCHAR(26) NULL
,role_name VARCHAR(30) NULL
,server_role VARCHAR(30) NULL
);
IF OBJECT_ID('Tempdb.dbo.#ServerRole', 'u') IS NOT NULL
DROP TABLE dbo.#ServerRole
CREATE TABLE dbo.#ServerRole
(
ServerRole VARCHAR(50) NULL
,UserName VARCHAR(70) NULL
,TypeDesc VARCHAR(40) NULL
)
CREATE INDEX EI1UserName ON dbo.#ServerRole (UserName);
INSERT dbo.#ServerRole (ServerRole, UserName, TypeDesc)
SELECT CAST(suser_name(rm.role_principal_id) AS VARCHAR(25)) AS [ServerRole]
,CAST(suser_name(rm.member_principal_id) AS VARCHAR(65)) AS [UserName]
,CAST(u.type_desc AS VARCHAR(20)) AS [TypeDesc]
FROM sys.server_role_members rm
JOIN sys.server_principals AS p
ON rm.role_principal_id = p.principal_id
JOIN sys.server_principals AS u
ON rm.member_principal_id = u.principal_id
WHERE (RTRIM(u.name) NOT IN ('NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt', 'NT Service\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT', 'Home\SQLService', 'sa') )
ORDER BY 'UserName' ASC, 'ServerRole' ASC, 'TypeDesc' ASC;
PRINT '-- dbo.#ServerRole'
SELECT * FROM dbo.#ServerRole ORDER BY UserName ASC;
IF OBJECT_ID('Tempdb..#MemberRole', 'u') IS NOT NULL
DROP TABLE #MemberRole
CREATE TABLE #MemberRole
(
database_name VARCHAR(50) NULL,
principal_name VARCHAR(40) NULL,
principal_type_desc VARCHAR(40) NULL,
role_name VARCHAR(40) NULL
)
EXECute sp_MSforeachdb
'USE ?;
WITH perms_cte AS
( SELECT CAST(USER_NAME(p.grantee_principal_id) AS VARCHAR(40)) AS principal_name,
dp.principal_id,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
FROM sys.database_permissions AS p
JOIN sys.database_principals AS dp
ON p.grantee_principal_id = dp.principal_id
)
--users
INSERT #MemberRole (database_name, principal_name, principal_type_desc, role_name)
SELECT ''?'',
CAST(p.principal_name AS VARCHAR(40)),
CAST(p.principal_type_desc AS VARCHAR(40)),
CAST(CAST(NULL AS SYSNAME) AS VARCHAR(40))
FROM perms_cte AS p
WHERE (principal_type_desc <> ''DATABASE_ROLE'')
UNION
--role members
SELECT ''?'',
CAST(rm.member_principal_name AS VARCHAR(40)),
CAST(rm.principal_type_desc AS VARCHAR(40)),
CAST(rm.role_name AS VARCHAR(40))
FROM perms_cte AS p
RIGHT OUTER JOIN (SELECT role_principal_id,
dp.type_desc AS principal_type_desc,
member_principal_id,
user_name(member_principal_id) as member_principal_name,
user_name(role_principal_id) as role_name
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS dp
ON rm.member_principal_id = dp.principal_id
) AS rm
ON rm.role_principal_id = p.principal_id
ORDER BY p.principal_name ASC'
DELETE dbo.#MemberRole
WHERE (database_name IN ('model', 'master', 'tempdb', 'pubs', 'Northwind', 'msdb',
'ReportServer', 'ReportServerTempDB', 'AdventureWorksDW2012',
'AdventureWorks2012', 'DBAMaintenance'));
DELETE dbo.#MemberRole
WHERE (role_name IS NULL)
DELETE dbo.#MemberRole
WHERE (principal_name = 'dbo')
-- Below just for Report purpose.
IF OBJECT_ID('Tempdb.dbo.#FinalResult', 'u') IS NOT NULL
DROP TABLE dbo.#FinalResult
INSERT @FinalResult (seq_num, database_name, iuser_name, principal_type_desc, role_name)
SELECT ROW_NUMBER() OVER(PARTITION BY database_name ORDER BY database_name ASC) AS 'seq_num'
,database_name
,principal_name
,principal_type_desc
,role_name
FROM dbo.#MemberRole
WHERE (database_name = ISNULL(@DBName, database_name))
ORDER BY database_name ASC, seq_num ASC, principal_name ASC
-- stop Issues.
-- This section update if a user is in Server Role.
UPDATE a
SET server_role = b.ServerRole
FROM @FinalResult AS a
JOIN dbo.#ServerRole AS b
ON ( RTRIM(a.iuser_name) = RTRIM(b.UserName) );
IF (@UserName IS NOT NULL)
BEGIN
--PRINT '--Go to 1.'
SELECT --seq_num,
CAST(@@SERVERNAME AS VARCHAR(35)) AS 'servername'
,database_name AS [DatabaseName]
,iuser_name AS [UserName]
,principal_type_desc AS 'TypeDesc'
,role_name AS 'DatabaseRole'
,ISNULL(server_role, ' ') AS 'ServerRole'
FROM @FinalResult
WHERE (iuser_name = ISNULL(@UserName, iuser_name) )
END
ELSE
IF (@UserName IS NULL)
AND (@DBName IS NOT NULL)
BEGIN
--PRINT 'Go to 2.'
SELECT --seq_num,
CAST(@@SERVERNAME AS VARCHAR(35)) AS 'ServerName'
,database_name AS [DatabaseName]
,iuser_name AS [UserName]
,principal_type_desc AS 'TypeDesc'
,role_name AS 'DatabaseRole'
,ISNULL(server_role, ' ') AS 'ServerRole'
FROM @FinalResult
WHERE (database_name = ISNULL(@DBName, database_name) )
END
ELSE
IF (@UserName IS NULL)
AND (@DBName IS NULL)
BEGIN
--PRINT '--Go to 3.'
UPDATE @FinalResult
SET database_name = ' '
WHERE (seq_num > 1);
-- Output results.
SELECT --seq_num,
CAST(@@SERVERNAME AS VARCHAR(35)) AS 'ServerName'
,database_name AS [DatabaseName]
,iuser_name AS [UserName]
,principal_type_desc AS 'TypeDesc'
,role_name AS 'DatabaseRole'
,ISNULL(server_role, ' ') AS 'ServerRole'
FROM @FinalResult;
END
GO
---------------------------------------------------------------------------------------------------------
EXECute msdb.dbo.usp_AdminReturnUserAccessReport @UserName = 'ABC\LaNguyen'
,@dbname = 'prod1'
GO
Other recent topics
