TSQL :- Find Schema Uses And Roles

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 🙂



Categories: #kravis, #programming, Technology, TSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Loly Hallison

"In moments of crisis, only the imagination is more important than the knowledge" Albert Einstein

The Art of Blogging

For bloggers who aspire to inspire

creative art ✏ design that inspires😊

LIFE IS ART PAINT YOUR DREAMS ✏💗

Journeying Through My Thoughts

♡|Thoughts|Beautiful Words|Motivation|Poem's|Quote's|Maybe Short Storie's|Happiness|Joy|Soul Pouring|An Escape Patch|Memories|Home|♡

Manić Teodora

Al cielo.. e niente di meno!🌈

Voices on a paper

I often eavesdrop to random voices roaming in the air. Some leave bruises on my eyelids due to the frequent wiping of tears. They have a saddening picture. Others stimulate endless smiles. Some would like to remain unidentified. All in all, I love to narrate stories of the sad, happy and mysterious voices.

THE DREAM MAKER

Poem and motivational stories

Syra and her Partner's in Crime

Everything that adds up to your fashion trends ♥

%d bloggers like this: