USE [master]
GO
IF OBJECT_ID('dbo.sp_PermissionsDBList') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_PermissionsDBList AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_PermissionsDBList
@IncludeDBs VARCHAR(MAX) = NULL,
@ExcludeDBs VARCHAR(MAX) = NULL,
@InludeSystem BIT = 0,
@IncludeReadOnly BIT = 0,
@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,
@PrintCreate BIT = 0,
@PrintDelete BIT = 0,
@CreateUsersPath VARCHAR(1000) = NULL,
@DropUsersPath VARCHAR(1000) = NULL,
@AddRoleMembershipPath VARCHAR(1000) = NULL,
@DeleteRoleMembershipPath VARCHAR(1000) = NULL,
@AddPermissionPath VARCHAR(1000) = NULL,
@DeletePermissionPath VARCHAR(1000) = NULL,
@AllCreatePath VARCHAR(1000) = NULL,
@AllDeletePath VARCHAR(1000) = NULL,
@recipients NVARCHAR(1000) = NULL,
@subject NVARCHAR(1000) = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @SQL NVARCHAR(MAX)
DECLARE @Include TABLE (DBname SYSNAME NOT NULL
PRIMARY KEY CLUSTERED)
INSERT @Include
(DBName)
SELECT DISTINCT
Util.dbo.TrimBothEnds(PARSENAME(Field, 1)) AS DbName
FROM Util.dbo.ParseDelimited(REPLACE(REPLACE(REPLACE(@IncludeDBs, ' ', ','), ' ', ','), '
', ','), ',')
WHERE Field <> ''
DECLARE @IncludeCount INT = @@ROWCOUNT
DECLARE @Exclude TABLE (DBname SYSNAME NOT NULL
PRIMARY KEY CLUSTERED)
INSERT @Exclude
(DBName)
SELECT DISTINCT
Util.dbo.TrimBothEnds(PARSENAME(Field, 1)) AS DbName
FROM Util.dbo.ParseDelimited(REPLACE(REPLACE(REPLACE(@ExcludeDBs, ' ', ','), ' ', ','), '
', ','), ',')
WHERE Field <> ''
DECLARE @ExcludeCount INT = @@ROWCOUNT
SELECT @SQL = 'DECLARE @UserListTmp VARCHAR(MAX),
@CreateUsersTmp VARCHAR(MAX),
@DropUsersTmp VARCHAR(MAX),
@AddRoleMembershipTmp VARCHAR(MAX),
@DeleteRoleMembershipTmp VARCHAR(MAX),
@AddPermissionTmp VARCHAR(MAX),
@DeletePermissionTmp VARCHAR(MAX),
@AllCreateTmp VARCHAR(MAX),
@AllDeleteTmp VARCHAR(MAX)
SELECT @CreateUsersInp = '''', @DropUsersInp = '''', @AddRoleMembershipInp = '''', @DeleteRoleMembershipInp = '''', @AddPermissionInp = '''', @DeletePermissionInp = '''',
@AllCreateInp = '''', @AllDeleteInp = ''''
' + Util.dbo.StringConcat('BEGIN TRY
EXEC dbo.sp_permissions
@DbName = ''' + name + ''',
@UserList = @UserListInp,
@CreateUsers = @CreateUsersTmp OUTPUT,
@DropUsers = @DropUsersTmp OUTPUT,
@AddRoleMembership = @AddRoleMembershipTmp OUTPUT,
@DeleteRoleMembership = @DeleteRoleMembershipTmp OUTPUT,
@AddPermission = @AddPermissionTmp OUTPUT,
@DeletePermission = @DeletePermissionTmp OUTPUT,
@AllCreate = @AllCreateTmp OUTPUT,
@AllDelete = @AllDeleteTmp OUTPUT,
@UseGoTerminator = @UseGoTerminatorInp,
@AddUseDBName = 1,
@PrintCreate = 0,
@PrintDelete = 0
SELECT
@CreateUsersInp = @CreateUsersInp + ISNULL(''-- DBNAME: ' + name + '
'' + @CreateUsersTmp, ''''),
@DropUsersInp = @DropUsersInp + ISNULL(''-- DBNAME: ' + name + '
'' + @DropUsersTmp, ''''),
@AddRoleMembershipInp = @AddRoleMembershipInp + ISNULL(''-- DBNAME: ' + name + '
'' + @AddRoleMembershipTmp, ''''),
@DeleteRoleMembershipInp = @DeleteRoleMembershipInp + ISNULL(''-- DBNAME: ' + name + '
'' + @DeleteRoleMembershipTmp, ''''),
@AddPermissionInp = @AddPermissionInp + ISNULL(''-- DBNAME: ' + name + '
'' + @AddPermissionTmp, ''''),
@DeletePermissionInp = @DeletePermissionInp + ISNULL(''-- DBNAME: ' + name + '
'' + @DeletePermissionTmp, ''''),
@AllCreateInp = @AllCreateInp + ISNULL(''-- DBNAME: ' + name + '
'' + @AllCreateTmp, ''''),
@AllDeleteInp = @AllDeleteInp + ISNULL(''-- DBNAME: ' + name + '
'' + @AllDeleteTmp, '''')
END TRY
BEGIN CATCH
SELECT ErrorNumber = ERROR_NUMBER(),
ErrorSeverity = ERROR_SEVERITY(),
ErrorState = ERROR_STATE(),
[Database] = DB_NAME(),
ErrorProcedure = ERROR_PROCEDURE(),
ErrorLine = ERROR_LINE(),
ErrorMessage = ERROR_MESSAGE(),
DBName = ''' + name + '''
END CATCH
', '')
FROM sys.databases d (NOLOCK)
WHERE (@InludeSystem = 1
OR (@InludeSystem = 0
AND NAME NOT IN ('master', 'tempdb', 'model', 'msdb')))
AND (@IncludeReadOnly = 1
OR (@IncludeReadOnly = 0
AND is_read_only = 0))
AND STATE = 0
AND ((@IncludeCount > 0
AND EXISTS ( SELECT *
FROM @Include
WHERE dbname = d.name ))
OR @IncludeCount = 0)
AND ((@ExcludeCount > 0
AND EXISTS ( SELECT *
FROM @Exclude
WHERE dbname = d.name ))
OR @ExcludeCount = 0)
EXEC sp_executesql
@SQL,
N'@UserListInp VARCHAR(MAX), @CreateUsersInp VARCHAR(MAX) OUTPUT, @DropUsersInp VARCHAR(MAX) OUTPUT, @AddRoleMembershipInp VARCHAR(MAX) OUTPUT, @DeleteRoleMembershipInp VARCHAR(MAX) OUTPUT,
@AddPermissionInp VARCHAR(MAX) OUTPUT, @DeletePermissionInp VARCHAR(MAX) OUTPUT, @AllCreateInp VARCHAR(MAX) OUTPUT, @AllDeleteInp VARCHAR(MAX) OUTPUT, @UseGoTerminatorInp BIT',
@UserList,
@CreateUsers OUTPUT,
@DropUsers OUTPUT,
@AddRoleMembership OUTPUT,
@DeleteRoleMembership OUTPUT,
@AddPermission OUTPUT,
@DeletePermission OUTPUT,
@AllCreate OUTPUT,
@AllDelete OUTPUT,
@UseGoTerminator
--#region Write Files
IF @CreateUsersPath <> ''
SELECT ReturnVal,
MESSAGE,
'CreateUsers' AS Type,
@CreateUsersPath AS FilePath,
'Saved to ' + @CreateUsersPath AS Info
FROM Util.FS.AppendAllTextToFile(@CreateUsersPath, @SQL, 1)
IF @DropUsersPath <> ''
SELECT ReturnVal,
MESSAGE,
'DropUsers' AS Type,
@DropUsersPath AS FilePath,
'Saved to ' + @DropUsersPath AS Info
FROM Util.FS.AppendAllTextToFile(@DropUsersPath, @SQL, 1)
IF @AddRoleMembershipPath <> ''
SELECT ReturnVal,
MESSAGE,
'AddRoleMembership' AS Type,
@AddRoleMembershipPath AS FilePath,
'Saved to ' + @AddRoleMembershipPath AS Info
FROM Util.FS.AppendAllTextToFile(@AddRoleMembershipPath, @SQL, 1)
IF @DeleteRoleMembershipPath <> ''
SELECT ReturnVal,
MESSAGE,
'DeleteRoleMembership' AS Type,
@DeleteRoleMembershipPath AS FilePath,
'Saved to ' + @DeleteRoleMembershipPath AS Info
FROM Util.FS.AppendAllTextToFile(@DeleteRoleMembershipPath, @SQL, 1)
IF @AddPermissionPath <> ''
SELECT ReturnVal,
MESSAGE,
'AddPermission' AS Type,
@AddPermissionPath AS FilePath,
'Saved to ' + @AddPermissionPath AS Info
FROM Util.FS.AppendAllTextToFile(@AddPermissionPath, @SQL, 1)
IF @DeletePermissionPath <> ''
SELECT ReturnVal,
MESSAGE,
'DeletePermissionPath' AS Type,
@DeletePermissionPath AS FilePath,
'Saved to ' + @DeletePermissionPath AS Info
FROM Util.FS.AppendAllTextToFile(@DeletePermissionPath, @SQL, 1)
IF @AllCreatePath <> ''
SELECT ReturnVal,
MESSAGE,
'AllCreate' AS Type,
@AllCreatePath AS FilePath,
'Saved to ' + @AllCreatePath AS Info
FROM Util.FS.AppendAllTextToFile(@AllCreatePath, @SQL, 1)
IF @AllDeletePath <> ''
SELECT ReturnVal,
MESSAGE,
'AllDelete' AS Type,
@AllDeletePath AS FilePath,
'Saved to ' + @AllDeletePath AS Info
FROM Util.FS.AppendAllTextToFile(@AllDeletePath, @SQL, 1)
--#endregion
IF @recipients <> ''
BEGIN
DECLARE @AllSQL VARCHAR(MAX)= @AllDelete + @AllCreate
SET @subject = ISNULL(@subject, 'sp_PermissionsDBList')
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_PermissionsDBList
GO
RETURN
DECLARE @ReturnVal INT,
@IncludeDBs VARCHAR(MAX) = NULL /*DEFAULT=NULL*/,
@ExcludeDBs VARCHAR(MAX) = NULL /*DEFAULT=NULL*/,
@InludeSystem BIT = 0 /*DEFAULT=0*/,
@IncludeReadOnly BIT = 0 /*DEFAULT=0*/,
@UserList VARCHAR(MAX) = NULL /*DEFAULT=NULL*/,
@CreateUsers VARCHAR(MAX),
@DropUsers VARCHAR(MAX),
@AddRoleMembership VARCHAR(MAX),
@DeleteRoleMembership VARCHAR(MAX),
@AddPermission VARCHAR(MAX),
@DeletePermission VARCHAR(MAX),
@AllCreate VARCHAR(MAX),
@AllDelete VARCHAR(MAX),
@UseGoTerminator BIT = 0 /*DEFAULT=0*/,
@PrintCreate BIT = 1 /*DEFAULT=0*/,
@PrintDelete BIT = 1 /*DEFAULT=0*/,
@CreateUsersPath VARCHAR(1000) = NULL /*DEFAULT=NULL*/,
@DropUsersPath VARCHAR(1000) = NULL /*DEFAULT=NULL*/,
@AddRoleMembershipPath VARCHAR(1000) = NULL /*DEFAULT=NULL*/,
@DeleteRoleMembershipPath VARCHAR(1000) = NULL /*DEFAULT=NULL*/,
@AddPermissionPath VARCHAR(1000) = NULL /*DEFAULT=NULL*/,
@DeletePermissionPath VARCHAR(1000) = NULL /*DEFAULT=NULL*/,
@AllCreatePath VARCHAR(1000) = NULL /*DEFAULT=NULL*/,
@AllDeletePath VARCHAR(1000) = NULL
/*DEFAULT=NULL*/
EXEC @ReturnVal = dbo.sp_PermissionsDBList
@IncludeDBs = @IncludeDBs,
@ExcludeDBs = @ExcludeDBs,
@InludeSystem = @InludeSystem,
@IncludeReadOnly = @IncludeReadOnly,
@UserList = @UserList,
@CreateUsers = @CreateUsers OUTPUT,
@DropUsers = @DropUsers OUTPUT,
@AddRoleMembership = @AddRoleMembership OUTPUT,
@DeleteRoleMembership = @DeleteRoleMembership OUTPUT,
@AddPermission = @AddPermission OUTPUT,
@DeletePermission = @DeletePermission OUTPUT,
@AllCreate = @AllCreate OUTPUT,
@AllDelete = @AllDelete OUTPUT,
@UseGoTerminator = @UseGoTerminator,
@PrintCreate = @PrintCreate,
@PrintDelete = @PrintDelete,
@CreateUsersPath = @CreateUsersPath,
@DropUsersPath = @DropUsersPath,
@AddRoleMembershipPath = @AddRoleMembershipPath,
@DeleteRoleMembershipPath = @DeleteRoleMembershipPath,
@AddPermissionPath = @AddPermissionPath,
@DeletePermissionPath = @DeletePermissionPath,
@AllCreatePath = @AllCreatePath,
@AllDeletePath = @AllDeletePath
GO