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
JustSQL 