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 SchemaOwnerFROM sys.objects soINNER JOIN sys.schemas ss ON so.schema_id = ss.schema_idINNER JOIN sysusers su ON ss.principal_id = su.uidWHERE 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 CreateStatementTextFROM #DatabaseRolesORDER 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 CreateStatementTextFROM #DatabaseSchemasORDER 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 CreateStatementTextFROM #DatabaseUsersORDER BY DBUserName

SELECT ResultText AS ' 'FROM #ResultsORDER BY ResultID

Page updated

Google Sites

Report abuse