Remove Permissions from a Database

Applicability:

                 SQL Server 2000:        N/A
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Tested
                 SQL Server 2014:        Tested
                 SQL Server 2016:        Tested
                 SQL Server 2017:        Not Tested          

Credits:

Author: ChillyDBA
Date:    7 Jun 2020

Description

Generates TSQL Scripts to remove all security from a single DB
This does not remove server-level security objects  (e.g. Logins)
 

GENERATE script on:     SOURCE SERVER (per USER database to be migrated)
EXECUTE script on:       SOURCE SERVER - NOTE:  Only do this if you no longer required the SOURCE DB to be fully usable

Code


SET NOCOUNT ON /* Purpose: Script to remove all security/rights from a single database Intended for use when refreshing one environment from another eg PROD permissions would be removed and SIG ones applied when refreshing from PROD to SIG Scope includes: Delete DB Schemas - but only when they do not own objects Delete DB roles Delete DB users, including orphaned users No requirement to remove the following Server logins Users to roles mappings - these will go when the roles go All permissions (role, user, user defined types) - these will all go when the users, schemas and roles are deleted Output Data Each data set is populated from the viewpoint of the current database Each data set includes key attributes on the subject item, plus the TSQL code to recreate the item on any server/database DatabaseUsers - all database users NOTE: Orphan users (with no server login) and non-mapped (with mis-matched SID) will be identified and deleted also DatabaseRoles - all non-system database roles DatabaseSchemas - all non-system schemas that do not own objects Author: Originally wsoranno@winona.edu and choffman Augmented by ChillyDBA History: 16 Oct 2018 - ChillyDBA - Initial Issue */ /***************************************************************** *** Variable Declarations *** ******************************************************************/ DECLARE --general use @SQLStatement VARCHAR(MAX) ,@Sort INT -- loop variables ,@DatabaseName NVARCHAR(256) ,@ServerName NVARCHAR(256) ,@ServerLoginID INT ,@CRLF VARCHAR(10) ,@TAB VARCHAR(10) SELECT @Servername = @@SERVERNAME ,@DatabaseName = DB_NAME() SELECT @CRLF = CHAR(13) + CHAR(10) ,@TAB = CHAR(9) /***************************************************************** *** Temp Table Definitions *** ******************************************************************/ IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#DatabaseUsers%' AND type = 'u') DROP TABLE #DatabaseUsers CREATE TABLE #DatabaseUsers ( DBUserID INT IDENTITY(1,1) ,ServerName NVARCHAR(256) ,DatabaseName NVARCHAR(256) ,DBUserName NVARCHAR(256) ,DBUserSID VARBINARY(85) ,CreateStatementText VARCHAR(MAX) ,UserHasNoServerLogin INT ,UserRequiresSIDFix INT ) IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#DatabaseRoles%' AND type = 'u') DROP TABLE #DatabaseRoles CREATE TABLE #DatabaseRoles ( DBRoleID INT IDENTITY(1,1) ,ServerName NVARCHAR(256) ,DatabaseName NVARCHAR(256) ,DatabaseRoleName NVARCHAR(256) ,DateCreated DATETIME ,DateModified DATETIME ,CreateStatementText VARCHAR(MAX) ) IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#DatabaseSchemas%' AND type = 'u') DROP TABLE #DatabaseSchemas CREATE TABLE #DatabaseSchemas ( DBSchemaID INT IDENTITY(1,1) ,ServerName NVARCHAR(256) ,DatabaseName NVARCHAR(256) ,DatabaseSchemaName NVARCHAR(256) ,CreateStatementText VARCHAR(MAX) ) IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#SchemasOwningObjects%' AND type = 'u') DROP TABLE #SchemasOwningObjects CREATE TABLE #SchemasOwningObjects ( SchemaName NVARCHAR(256) ,SchemaOwner NVARCHAR(256) ) IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name like '#Results%' AND type = 'U') DROP TABLE #Results CREATE TABLE #Results ( ResultID INT IDENTITY(1,1) ,ResultText VARCHAR(MAX) ) -- get a list of schemas and their owners that actually own objects in the DB -- these should be excluded from any deletion activity INSERT #SchemasOwningObjects ( SchemaName ,SchemaOwner ) SELECT DISTINCT SCHEMA_NAME(so.schema_id) AS SchemaName ,su.name AS SchemaOwner FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id INNER JOIN sysusers su ON ss.principal_id = su.uid WHERE SCHEMA_NAME(so.schema_id) NOT IN ('dbo', 'sys') /*********************************************************/ /********* START GET DB USERS *********/ /*********************************************************/ SELECT @SQLStatement = '' SELECT @SQLStatement = @SQLStatement + ' USE [' + @DatabaseName + '] INSERT #DatabaseUsers ( ServerName ,DatabaseName ,DBUserName ,DBUserSID ,CreateStatementText ,UserHasNoServerLogin ,UserRequiresSIDFix ) SELECT ''' + @Servername + ''' AS ServerName ,''' + @DatabaseName + ''' AS DatabaseName ,p.[name] AS DBUserName ,p.[SID] AS DBUserSID ,''IF EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '' + SPACE(1) + '''''''' + p.[name] + '''''''' + '') BEGIN DROP USER '' + SPACE(1) + QUOTENAME(p.[name]) + SPACE(1) + ''END; '' AS CreateStatementText ,CASE WHEN sp1.name IS NULL THEN 1 ELSE 0 END AS UserHasNoServerLogin ,CASE WHEN sp2.name IS NULL AND sp1.name IS NOT NULL THEN 1 ELSE 0 END AS UserRequiresSIDFix FROM sys.database_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name COLLATE SQL_Latin1_General_CP1_CI_AI ) INNER JOIN sysusers s on p.sid = s.sid LEFT OUTER JOIN sys.server_principals sp1 ON p.name COLLATE SQL_Latin1_General_CP1_CI_AI= sp1.name LEFT OUTER JOIN sys.server_principals sp2 ON p.name COLLATE SQL_Latin1_General_CP1_CI_AI= sp2.name AND p.SID = sp2.SID WHERE p.type IN ( ''S'', ''G'', ''U'' ) AND p.name NOT IN (''sa'', ''public'', ''guest'', ''dbo'' ) AND p.name NOT IN (SELECT SchemaOwner FROM #SchemasOwningObjects) ORDER BY p.name ' EXEC(@SqlStatement) /*********************************************************/ /********* END GET DB USERS *********/ /*********************************************************/ /*********************************************************/ /********* START GET DB ROLES *********/ /*********************************************************/ SELECT @SQLStatement = '' SELECT @SQLStatement = @SQLStatement + ' USE [' + @DatabaseName + '] INSERT #DatabaseRoles ( Servername ,DatabaseName ,DatabaseRoleName ,DateCreated ,DateModified ,CreateStatementText ) SELECT ''' + @Servername + ''' AS ServerName ,''' + @DatabaseName + ''' AS DatabaseName ,dp.name AS DatabaseRoleName ,dp.create_date AS DateCreated ,dp.modify_date AS DateModified ,''IF EXISTS (SELECT * FROM sys.database_principals WHERE type_desc = ''''DATABASE_ROLE'''' AND name = '''''' + dp.name + '''''')'' + '' EXEC sp_droprole @rolename ='' + SPACE(1) + QUOTENAME((dp.name), '''''''') AS CreateStatementText FROM sys.database_principals dp WHERE [type] IN (''R'') -- R = Database Role AND [principal_id] > 4 -- 0 to 4 are system users/schemas AND is_fixed_role = 0 ' EXEC(@SqlStatement) /*********************************************************/ /********* END GET DB ROLES *********/ /*********************************************************/ /*********************************************************/ /********* START GET DB SCHEMAS *********/ /*********************************************************/ SELECT @SQLStatement = '' SELECT @SQLStatement = @SQLStatement + ' USE [' + @DatabaseName + '] INSERT #DatabaseSchemas ( ServerName ,DatabaseName ,DatabaseSchemaName ,CreateStatementText ) SELECT ''' + @Servername + ''' AS ServerName ,''' + @DatabaseName + ''' AS DatabaseName ,ss.Name AS DatabaseSchemaName ,''IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'''''' + ss.name + '''''')'' + '' EXEC sys.sp_executesql N''''DROP SCHEMA'' + SPACE(1) + QUOTENAME((ss.name)) + '''''''' AS CreateStatementText FROM sys.schemas ss INNER JOIN sys.database_principals dp ON ss.principal_id = dp.principal_id WHERE dp.[principal_id] > 4 -- 0 to 4 are system users/schemas AND is_fixed_role = 0 AND ss.name NOT IN (SELECT SchemaName FROM #SchemasOwningObjects) ' EXEC(@SqlStatement) /*********************************************************/ /********* END GET DB SCHEMAS *********/ /*********************************************************/ --select * from #ServerLoginsForDB order by servername, DatabaseName --select * from #DatabaseUsers order by servername, DatabaseName --select * from #DatabaseRoles order by servername, DatabaseName --select * from #DatabaseSchemas order by servername, DatabaseName --select * from #DatabaseRoleMembers order by servername, DatabaseName --select * from #DatabaseObjectPermissions order by servername, DatabaseName --select * from #DatabaseLevelPermissions order by servername, DatabaseName --select * from #SchemaLevelPermissions order by servername, DatabaseName -- Now the section to create the output INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) VALUES ('/**********************************************************************/') INSERT #Results(ResultText) VALUES ('/****** CREATE Database Roles ******/') INSERT #Results(ResultText) VALUES ('/**********************************************************************/') INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) VALUES ('USE [' + @DatabaseName + ']') INSERT #Results(ResultText) VALUES ('GO') INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) SELECT CreateStatementText FROM #DatabaseRoles ORDER BY DatabaseRoleName INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) VALUES ('/**********************************************************************/') INSERT #Results(ResultText) VALUES ('/****** CREATE Database Schemas ******/') INSERT #Results(ResultText) VALUES ('/**********************************************************************/') INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) VALUES ('USE [' + @DatabaseName + ']') INSERT #Results(ResultText) VALUES ('GO') INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) SELECT CreateStatementText FROM #DatabaseSchemas ORDER BY DatabaseSchemaName INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) VALUES ('/**********************************************************************/') INSERT #Results(ResultText) VALUES ('/****** CREATE Database Users ******/') INSERT #Results(ResultText) VALUES ('/**********************************************************************/') INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) VALUES ('USE [' + @DatabaseName + ']') INSERT #Results(ResultText) VALUES ('GO') INSERT #Results(ResultText) VALUES ('') INSERT #Results(ResultText) SELECT CreateStatementText FROM #DatabaseUsers ORDER BY DBUserName SELECT ResultText AS ' ' FROM #Results ORDER BY ResultID





Comments