SQL Server User Security Report Script

I needed a way to report on what permissions are assigned to a user on SQL Server. For added bonus I wanted to be able to specify a group and then list out all the users in that group with their permissions. As a double added bonus I wanted to set a flag so you could generate the DDL for a new user in case you want to duplicate the permissions to a new user or group.

Bingo. First version here. I am sure there are several versions of this on the web, but at the moment this one is working well for me.



SET QUOTED_IDENTIFIER OFF
GO

IF OBJECT_ID('dbo.UserSecurityReport') IS NULL
    EXEC ('CREATE PROCEDURE dbo.UserSecurityReport AS SELECT 0')
GO
/*
*********************************************************************************************************
*
* Date:				8/27/2014			
* Purpose: 			Generate a Security report server wide or for specific user
* Parameters: 		@Username - if you pass a user name the report will only return permissions for 
					that user. If you leave this NULL you will get a system wide report
*
*********************************************************************************************************
*/
ALTER PROCEDURE [dbo].[UserSecurityReport]
@Username VARCHAR(250) = NULL,
@CreateDDL CHAR(1) = 'N',
@Newuser VARCHAR(250) = NULL,
@ExpandGroup CHAR(1) = 'N'
AS

SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @db SYSNAME,
 @scope VARCHAR(16),
 @sql NVARCHAR(MAX),
 @MemberName VARCHAR(250);

DECLARE @member TABLE (
 RoleName SYSNAME,
 MemberName SYSNAME NULL,
 MemberSid VARBINARY(128)
);

DECLARE @report TABLE (
 ScopeID TINYINT,
 ScopeLevel VARCHAR(16),
 ScopeName SYSNAME,
 RoleName SYSNAME,
 MemberName SYSNAME NULL,
 ObjectName SYSNAME,
 PermType VARCHAR(500),
 PermState VARCHAR(50)
);

DECLARE @userList TABLE
(
account_name VARCHAR(400),
[type] VARCHAR(50),
[privilege] VARCHAR(50),
mapped_login_name VARCHAR(200),
permission_path VARCHAR(400)
)

DECLARE @exceptionList TABLE
(
groupName VARCHAR(400),
ErrorNumber VARCHAR(200),
ErrorMessage VARCHAR(2000)

)


SELECT @db = @@SERVERNAME, @scope = 'SERVER';

INSERT INTO @member ([RoleName], [MemberName], [MemberSid])
EXEC sp_helpsrvrolemember;


INSERT INTO @report ([ScopeID], [ScopeLevel], [ScopeName], [RoleName], [MemberName], [ObjectName], [PermType], [PermState])
SELECT 1, @scope, @db, RoleName, MemberName, 'N/A', 'N/A', 'N/A' FROM @member

DECLARE db_csr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT [name], 'DATABASE'
FROM [master].sys.databases
WHERE [database_id] > 4
AND ( [state] = 0
 OR ( [state] = 1 AND [is_in_standby] = 1 ) )
AND [source_database_id] IS NULL
AND LOWER([name]) NOT IN ('admin', 'distribution', 'ops')
AND LOWER([name]) NOT LIKE 'adventurework%'
AND LOWER([name]) NOT LIKE 'reportserv%'
ORDER BY [name];

OPEN db_csr;

WHILE 0 = 0
BEGIN
 DELETE FROM @member;

 FETCH NEXT FROM db_csr INTO @db, @scope;
 
 IF @@FETCH_STATUS <> 0 BREAK;

 SET @sql = " USE [" + @db + "]; 
 SELECT USER_NAME(role_principal_id), USER_NAME(member_principal_id), 0 
 FROM [" + @db + "].sys.database_role_members drm 
 WHERE LOWER(USER_NAME(role_principal_id)) NOT LIKE 'msrepl%' ";
 
 INSERT INTO @member ([RoleName], [MemberName], [MemberSid])
 EXEC sp_ExecuteSQL @sql;

 INSERT INTO @member ([RoleName], [MemberName], [MemberSid])
 SELECT 'explicit_owner', SUSER_SNAME([owner_sid]), [owner_sid]
 FROM [master].sys.databases
 WHERE [name] = @db;

 INSERT INTO @report ([ScopeID], [ScopeLevel], [ScopeName], [RoleName], [MemberName], [ObjectName], [PermType], [PermState])
 SELECT 2, @scope, @db, RoleName, MemberName, 'N/A', 'N/A', 'N/A' FROM @member;
 
 SET @sql = " USE [" + @db + "]; 
 SELECT 3, p.class_desc, DB_NAME(), 'N/A', USER_NAME(p.grantee_principal_id),ISNULL(OBJECT_NAME(p.major_id), 'N/A'), p.permission_name, p.state_desc 
 FROM sys.database_permissions p 
 INNER JOIN sys.database_principals dp 
 ON p.grantee_principal_id = dp.principal_id 
 WHERE LOWER(USER_NAME(p.grantee_principal_id)) NOT IN ('dbo','guest','public') ";
 
 INSERT INTO @report ([ScopeID], [ScopeLevel], [ScopeName], [RoleName], [MemberName], [ObjectName], [PermType], [PermState])
 EXEC sp_ExecuteSQL @sql;
END

CLOSE db_csr;
DEALLOCATE db_csr;

DELETE FROM @report WHERE LOWER(MemberName) IN ('dbo', 'sa');
IF @ExpandGroup = 'N'
BEGIN
 SELECT * FROM @report where (@Username IS NULL OR membername = @Username) ORDER BY 1, 2, 3, 4, 5, 6, 7 ;
END

IF @ExpandGroup = 'Y'
BEGIN
 BEGIN TRY
 INSERT INTO @userList
 EXEC ('EXEC xp_logininfo ''' + @Username + ''',''members''')
 END TRY
 BEGIN CATCH
 INSERT INTO @exceptionList
 SELECT @username, ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage
 END CATCH

 SELECT r.ScopeID,r.ScopeLevel,r.ScopeName,r.RoleName,u.mapped_login_name,r.ObjectName,r.PermType,r.PermState
 FROM @userList u JOIN @report r ON u.permission_path = r.MemberName
 ORDER BY u.mapped_login_name


END


IF @CreateDDL = 'Y'
BEGIN

IF @Newuser IS NOT NULL
 SET @MemberName = @Newuser
ELSE
 SET @MemberName = @UserName



DECLARE @versionCheck TINYINT;
--Check Version Number
--If the SQL version is < 10 meaning pre 2008 then we set the Version Check to 0
--We will use this to determine if to deploy certain 2005 compatible objects
IF (SELECT CAST(REPLACE(SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('productversion')), 1, 2),'.','') AS INT)) <= 10
 SET @versionCheck = 0
ELSE
 SET @versionCheck = 1


PRINT "Member Name: " + @MemberName

IF @versionCheck = 1
BEGIN
 SELECT "ALTER SERVER ROLE [" + RoleName + "] ADD MEMBER [" + @MemberName + "]"
 FROM @Report
 WHERE ScopeID = 1
 AND MemberName = @UserName
END
ELSE
 IF @versionCheck = 0
 BEGIN
 SELECT "EXEC master..sp_addsrvrolemember '" + @MemberName + "', '" + RoleName + "'"
 FROM @Report
 WHERE ScopeID = 1
 AND MemberName = @UserName
 END

IF @versionCheck = 1
BEGIN
 SELECT "CREATE USER [" + @MemberName + "] FOR LOGIN [" + @MemberName + "]
 GO
 USE [" + ScopeName + "]
 GO
 ALTER ROLE [" + RoleName + "] ADD MEMBER [" + @MemberName + "]
 GO"
 FROM @report
 WHERE ScopeID = 2
 AND MemberName = @UserName
END
ELSE 
IF @versionCheck = 0
BEGIN
 SELECT "CREATE USER [" + @MemberName + "] FOR LOGIN [" + @MemberName + "]
 GO
 USE [" + ScopeName + "]
 GO
 EXEC sp_addrolemember ['" + RoleName + "'], ['" + @MemberName + "']
 GO"
 FROM @report
 WHERE ScopeID = 2
 AND MemberName = @UserName

END


SELECT "USE [" + ScopeName + "]
 GO
 GRANT CONNECT TO [" + @MemberName + "]
 GO"
FROM @Report
WHERE ScopeID = 3
AND MemberName = @UserName

END
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s