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 )

w

Connecting to %s

Invest In YourSelf First!!

Only We Can Change our Life, No one Else Do it For Us

Beauty Box Subscription Reviews and Giveaways

Beauty Product Review and Giveaways

Entrepreneurship

Business Owner, Entrepreneur, Author

Lorelle on WordPress

utorials about WordPress, blogging, social media, and having your say on the web.

LAVANYA'S BLOG

Where health meets poetry and blogs...

What a Beautiful Mess!

A virtual girl's guide to Second Life fashion!

Pics by Peep

A photo album of a virtual life

%d bloggers like this: