USE [master]
GO
IF OBJECT_ID('dbo.sp_permissions') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_permissions AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_permissions
@DbName VARCHAR(MAX) = NULL,
@UserList VARCHAR(MAX) = NULL,
@CreateUsers VARCHAR(MAX) = '' OUTPUT,
@DropUsers VARCHAR(MAX) = '' OUTPUT,
@AddRoleMembership VARCHAR(MAX) = '' OUTPUT,
@DeleteRoleMembership VARCHAR(MAX) = '' OUTPUT,
@AddPermission VARCHAR(MAX) = '' OUTPUT,
@DeletePermission VARCHAR(MAX) = '' OUTPUT,
@AllCreate VARCHAR(MAX) = '' OUTPUT,
@AllDelete VARCHAR(MAX) = '' OUTPUT,
@UseGoTerminator BIT = 0,
@AddUseDBName BIT = 0,
@PrintCreate BIT = 0,
@PrintDelete BIT = 0,
@recipients NVARCHAR(1000) = NULL,
@subject NVARCHAR(1000) = NULL,
@FilePath VARCHAR(4000) = NULL /* Will write the generated sql to the path */
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET @DbName = ISNULL(@DbName, DB_NAME())
DECLARE @BatchTerminator VARCHAR(10) = CASE WHEN @UseGoTerminator = 1 THEN '
GO
' + CASE WHEN @AddUseDBName = 1 THEN 'USE [' + @DBname + ']
' ELSE ''
END ELSE '
'
END
DECLARE @SQL NVARCHAR(MAX) = 'DECLARE @Users TABLE (UserName VARCHAR(500))
INSERT @Users
(UserName)
SELECT REPLACE(REPLACE(LTRIM(RTRIM(Field)), '']'', ''''), ''['', '''') AS UserName
FROM Util.dbo.ParseDelimited(@UserList, '','') a
CROSS APPLY (SELECT LTRIM (RTRIM (Field)) AS Trimmed) b
DECLARE @UserCount INT = @@ROWCOUNT
-- USERS ROLES
SELECT @CreateUsers = Util.dbo.StringConcat(''IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name COLLATE SQL_Latin1_General_CP1_CI_AS = N'''''' + dp.name + '''''')
'' + CASE dp.[Type]
WHEN ''G''
THEN ''CREATE USER ['' + dp.name + ''] FOR LOGIN ['' + sp.name + '']'' + ISNULL(''WITH DEFAULT_SCHEMA=[''
+ dp.default_schema_name + '']'',
'''')
WHEN ''U''
THEN ''CREATE USER ['' + dp.name + ''] FOR LOGIN ['' + sp.name + '']'' + ISNULL(''WITH DEFAULT_SCHEMA=[''
+ dp.default_schema_name + '']'',
'''')
WHEN ''S''
THEN ''CREATE USER ['' + dp.name + ''] FOR LOGIN ['' + sp.name + '']'' + ISNULL(''WITH DEFAULT_SCHEMA=[''
+ dp.default_schema_name + '']'',
'''')
WHEN ''R'' THEN ''CREATE ROLE ['' + dp.name + '']'' + ISNULL('' AUTHORIZATION ['' + op.name + '']'', '''')
END, @BatchTerminator) + @BatchTerminator,
@DropUsers = Util.dbo.StringConcat(''IF EXISTS (SELECT * FROM sys.database_principals WHERE name COLLATE SQL_Latin1_General_CP1_CI_AS = N'''''' + dp.name + '''''')
'' + CASE dp.[Type]
WHEN ''G'' THEN ''DROP USER ['' + dp.name + '']''
WHEN ''U'' THEN ''DROP USER ['' + dp.name + '']''
WHEN ''S'' THEN ''DROP USER ['' + dp.name + '']''
WHEN ''R'' THEN ''DROP ROLE ['' + dp.name + '']''
END, @BatchTerminator) + @BatchTerminator
FROM ' + @DbName + '.sys.database_principals AS dp
LEFT OUTER JOIN sys.server_principals sp ON sp.sid = dp.sid
LEFT OUTER JOIN ' + @DbName
+ '.sys.database_principals op ON dp.owning_principal_id = op.principal_id
LEFT OUTER JOIN @Users u ON dp.name COLLATE SQL_Latin1_General_CP1_CI_AS LIKE u.UserName
WHERE dp.is_fixed_role = 0
AND dp.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''public'', ''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')
AND ((@UserCount > 0
AND u.UserName IS NOT NULL)
OR (@UserCount = 0
AND u.UserName IS NULL)) ;
-- ROLE MEMBERS
SELECT @AddRoleMembership = Util.dbo.StringConcat(''EXEC sp_addrolemember N'''''' + rp.name + '''''', N'''''' + mp.name + '''''''', @BatchTerminator) + @BatchTerminator,
@DeleteRoleMembership = Util.dbo.StringConcat(''EXEC sp_droprolemember N'''''' + rp.name + '''''', N'''''' + mp.name + '''''''', @BatchTerminator) + @BatchTerminator
FROM ' + @DbName + '.sys.database_role_members a
INNER JOIN ' + @DbName + '.sys.database_principals rp ON rp.principal_id = a.role_principal_id
INNER JOIN ' + @DbName + '.sys.database_principals AS mp ON mp.principal_id = a.member_principal_id
LEFT OUTER JOIN @Users u ON mp.name COLLATE SQL_Latin1_General_CP1_CI_AS LIKE u.UserName
WHERE mp.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')
AND ((@UserCount > 0
AND u.UserName IS NOT NULL)
OR (@UserCount = 0
AND u.UserName IS NULL)) ;
WITH Perm
AS (SELECT d.class,
d.class_desc,
p.name AS UserName,
s.name AS GrantedObject,
StateDesc + '' '' + PermissionName + '' ON SCHEMA::['' + s.name + ''] TO ['' + p.name + ''];'' AS ApplySQL,
''REVOKE '' + PermissionName + '' ON SCHEMA::['' + s.name + ''] TO ['' + p.name + ''];'' AS RemoveSQL
FROM ' + @DbName + '.sys.database_permissions d
INNER JOIN ' + @DbName + '.sys.database_principals p ON d.grantee_principal_id = p.principal_id
INNER JOIN ' + @DbName + '.sys.schemas AS s ON s.schema_id = d.major_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca
WHERE d.class = 3 /*''SCHEMA''*/
AND p.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')
UNION ALL
SELECT d.class,
d.class_desc,
p.name AS UserName,
DB_NAME() AS GrantedObject,
StateDesc + '' '' + PermissionName + '' TO ['' + p.name + ''];'' AS ApplySQL,
''REVOKE '' + PermissionName + '' TO ['' + p.name + ''];'' AS RemoveSQL
FROM ' + @DbName + '.sys.database_permissions d
INNER JOIN ' + @DbName
+ '.sys.database_principals p ON d.grantee_principal_id = p.principal_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca
WHERE d.class = 0 /*DATABASE*/
AND p.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')
UNION ALL
SELECT d.class,
d.class_desc,
p.name AS UserName,
''['' + s.name + ''].['' + o.name + '']'' + ISNULL('' (['' + co.NAME + '']) '', '''') AS GrantedObject,
StateDesc + '' '' + PermissionName + '' ON ['' + s.name + ''].['' + o.name + ''] '' + ISNULL(''(['' + co.NAME + '']) '', '''') + ''TO ['' + p.name
+ ''];'' AS ApplySQL,
''REVOKE '' + PermissionName + '' ON ['' + s.name + ''].['' + o.name + ''] '' + ISNULL(''(['' + co.NAME + '']) '', '''') + ''TO ['' + p.name + ''];'' AS RemoveSQL
FROM ' + @DbName + '.sys.database_permissions d
INNER JOIN ' + @DbName + '.sys.database_principals p ON d.grantee_principal_id = p.principal_id
INNER JOIN ' + @DbName + '.sys.objects AS o ON o.object_id = d.major_id
INNER JOIN ' + @DbName + '.sys.schemas AS s ON s.schema_id = o.schema_id
LEFT OUTER JOIN ' + @DbName + '.sys.columns co ON co.object_id = o.object_id
AND co.column_id = d.minor_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca
WHERE d.class = 1 /*OBJECT OR COLUMN*/
AND p.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys''))
SELECT @AddPermission = Util.dbo.StringConcat(ApplySQL, @BatchTerminator) + @BatchTerminator,
@DeletePermission = Util.dbo.StringConcat(RemoveSQL, @BatchTerminator) + @BatchTerminator
FROM Perm p
LEFT OUTER JOIN @Users u ON p.UserName COLLATE SQL_Latin1_General_CP1_CI_AS LIKE u.UserName
WHERE (@UserCount > 0
AND u.UserName IS NOT NULL)
OR (@UserCount = 0
AND u.UserName IS NULL)
'
EXEC sp_executesql
@SQL,
N'@UserList VARCHAR(MAX),
@BatchTerminator VARCHAR(10),
@CreateUsers VARCHAR(MAX) OUTPUT,
@DropUsers VARCHAR(MAX) OUTPUT,
@AddRoleMembership VARCHAR(MAX) OUTPUT,
@DeleteRoleMembership VARCHAR(MAX) OUTPUT,
@AddPermission VARCHAR(MAX) OUTPUT,
@DeletePermission VARCHAR(MAX) OUTPUT',
@UserList,
@BatchTerminator,
@CreateUsers OUTPUT,
@DropUsers OUTPUT,
@AddRoleMembership OUTPUT,
@DeleteRoleMembership OUTPUT,
@AddPermission OUTPUT,
@DeletePermission OUTPUT
IF @AddUseDBName = 1
SELECT @CreateUsers = 'USE [' + @DbName + ']
' + @CreateUsers,
@DropUsers = 'USE [' + @DbName + ']
' + @DropUsers,
@AddRoleMembership = 'USE [' + @DbName + ']
' + @AddRoleMembership,
@DeleteRoleMembership = 'USE [' + @DbName + ']
' + @DeleteRoleMembership,
@AddPermission = 'USE [' + @DbName + ']
' + @AddPermission,
@DeletePermission = 'USE [' + @DbName + ']
' + @DeletePermission
SELECT @AllCreate = '-- Create users
' + ISNULL(@CreateUsers + @BatchTerminator, '
') + '-- Assign Role Memberships
' + ISNULL(@AddRoleMembership + @BatchTerminator, '
') + '-- Assign Permisions
' + ISNULL(@AddPermission, ''),
@AllDelete = '-- Revoke Permissions
' + ISNULL(@DeletePermission + @BatchTerminator, '
') + '-- Remove Role Membersips
' + ISNULL(@DeleteRoleMembership + @BatchTerminator, '
') + '-- Drop users
' + ISNULL(@DropUsers, '')
DECLARE @AllSQL VARCHAR(MAX)
IF @FilePath <> ''
BEGIN
SET @AllSQL = ISNULL(@AllSQL, @AllDelete + @AllCreate)
SELECT ReturnVal,
MESSAGE,
@FilePath AS FilePath,
'Saved to ' + @FilePath AS Info
FROM Util.FS.AppendAllTextToFile(@FilePath, @AllSQL, 1)
END
IF @recipients <> ''
BEGIN
SET @AllSQL = ISNULL(@AllSQL, @AllDelete + @AllCreate)
SET @subject = ISNULL(@subject, 'sp_permissions')
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@subject = @subject,
@body = @AllSQL
END
IF @PrintDelete = 1
BEGIN
PRINT '---- Delete SQL ----'
EXEC Util.dbo.PrintLargeText
@AllDelete
END
IF @PrintCreate = 1
BEGIN
PRINT '---- Create SQL ----'
EXEC Util.dbo.PrintLargeText
@AllCreate
END
GO
EXEC sys.sp_ms_marksystemobject
sp_permissions
GO