July 20, 2015 at 12:49 pm
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
July 20, 2015 at 1:28 pm
I can't test right now, but instead of doing an insert an update in here:
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) );
You might want to use a single INSERT with a JOIN.
INSERT @FinalResult (seq_num, database_name, iuser_name, principal_type_desc, role_name, server_role)
SELECT ROW_NUMBER() OVER(PARTITION BY m.database_name ORDER BY m.database_name ASC) AS 'seq_num'
,m.database_name
,m.principal_name
,m.principal_type_desc
,m.role_name
,s.ServerRole
FROM dbo.#MemberRole m
JOIN dbo.#ServerRole AS s ON m.principal_name = s.UserName
WHERE database_name = ISNULL(@DBName, database_name)
ORDER BY database_name ASC, seq_num ASC, principal_name ASC
July 20, 2015 at 2:04 pm
You might want to review your code as it has some issues that might cause problems. Such as the use of sp_MSforeachdb (Check an alternative[/url]), functions on columns in WHERE clauses, unnecessary work such as querying all databases when only one is needed, separate steps that can be consolidated, etc.
I'll try to take a look at this when I get home and I'm able to test a better solution.
July 20, 2015 at 2:50 pm
In looking through this, I see several logical problems with the query.
First, you are trying to have a row contain both database role and server role permissions. Why not have these separated?
Secondly, the rows need to advance, on both the database role and server role. i.e.:
Row1 - DatabaseRole1, ServerRole1
Row2 - DatabaseRole2, ServerRole2
In order to accomplish this, you need, for both the database role and the server role, a row number per principal, and then you need to also join these not only by the principal name, but also by this row number.
Currently, you are just updating based upon just the principal name. Since there is > 1 server role, all of the rows will get one of the server roles. Without an ORDER BY clause, it is undefined which of the server roles will be used.
Thirdly, let's look at what happens if there is not an equal number of Database roles and Server Roles.
1. Not as many Server Roles as there are Database Roles -
If you use a LEFT JOIN, some of the Database Role rows will have no value for Server Role.
If you use a JOIN, then the database role will be left out.
2. Not as many Database Roles as there are Server Roles - some of the Server Roles will be missing from the report.
Again, it would probably be easier to separate the database permissions from the server permissions. It would seem to make more sense that way (to me).
If you really want a row to have each principal, and then to have both the database and server permissions, I suggest that you separate these out into separate queries, and put all of the database permissions into one column (delimited by comma,space), and all of that principals server roles into a second column (again, delimited by comma,space). For both of these, I recommend using the FOR XML PATH(''), TYPE. I wrote an article for how to do this here[/url]. Have the server role query be a correlated subquery.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply