TSQL :- Find Schema Uses And Roles

By Joseph Kravis on November 28, 2017 • ( 0 )

I’ve done and on occasion import databases from others. Both, as a way to send content and receive content. To keep things in sync with users and to ensure that I’ve
captured the right users I run this script that I created sometime ago to assist with the process. You could further automate this by actually running the commands that are generated.
Just a simple script that can be copied and ran on your SQLSERVRER. This works on 2012/2014.

SET NOCOUNT ON

SELECT Mp.Type AS UserType, Mp.name AS UserName , Mp.default_Schema_name AS UserSchema
INTO #ModifySchemaTemp
FROM sys.database_role_members drm
join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
WHERE Mp.Type = ‘U’

–SELECT * FROM #ModifySchemaTemp

DECLARE @UserKey VARCHAR(50)
, @SchemaKey VARCHAR(80)
, @cnt INT
, @SchemaString VARCHAR(512)
, @UserString VARCHAR(512)

SELECT @cnt=COUNT(*) from #ModifySchemaTemp

WHILE(@cnt >0)
BEGIN
SELECT @UserKey=UserName from #ModifySchemaTemp
SELECT @SchemaKey=UserSchema from #ModifySchemaTemp

— Don’t delete the default schema for DBO
IF @SchemaKey <> ‘DBO’
BEGIN

SELECT @SchemaString = ‘IF EXISTS (SELECT * FROM sys.schemas WHERE name = N”’ + @SchemaKey + ”’)
DROP SCHEMA [‘+ @SchemaKey + ‘]
GO’
print @SchemaString

–DROP USER ACCOUNTS ONLY FROM NEWLY CREATED RESTORED DATABASE!
SELECT @UserString = ‘IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N”’+ @UserKey + ”’)
DROP USER [‘ + @UserKey + ‘]
GO’
PRINT @UserString

END
ELSE
BEGIN
–DROP USER ACCOUNTS ONLY FROM NEWLY CREATED RESTORED DATABASE!
SELECT @UserString = ‘IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N”’ + @UserKey + ”’)
DROP USER [‘ + @UserKey + ‘]
GO’

PRINT @UserString
END

–END

DELETE FROM #ModifySchemaTemp WHERE UserName=@UserKey
SELECT @cnt=COUNT(*) FROM #ModifySchemaTemp

END

DROP TABLE #ModifySchemaTemp

This produces a quick list that you can add to your documentation of the users on a database.

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'<SERVERNAME>\<SVCACCOUNT>’)
DROP USER [<SERVERNAME>\<SVCACCOUNT>]
GO
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'<SERVERNAME>\<USERACCOUNT>’)
DROP USER [<SERVERNAME>\<USERACCOUNT>]
GO
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’dbo’)
DROP USER [dbo]
GO

Please let me know the best way to provide code for your use. As I create samples I’d like to post them some place. I’ve setup a GIT account and will start posting code samples there.

Thoughts and Ideas,
Joseph 🙂