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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

What a Beautiful Mess!

A virtual girl's guide to Second Life fashion!

Pics by Peep

A photo album of a virtual life

VERSUS SL

SL FASHION AT ITS’ BEST. BE EXTRAORDINARY.

Aerlinniel's Wolftracks

Fashion, Landscaping, Furnishings, and Events in Second Life

What's New SL

What's New - New items released in Second Life

Who's Who

“Since everything is in our heads, we had better not lose them.” ― Coco Chanel

Online Website Reviews

Welcome to Online Website Reviews! Where you can get free online website reviews from real customers. The kind of reviews that you will find on our site is to. Business sites, web hosting sites, payment gateway sites, and even mystical websites. We also offer job search, phone services, blog reviews, electronics reviews, jewelry reviews, psychic reading reviews, and spell casting reviews.

jesussocial

Christian News, Devotional, Leadership, Church, Evangelism, Conference, Worship, Pastors , Bible, Gospel Music,Gospel,Salvation, GoodNews, Disciples, Cross,Winning, Love, Mercy,Bible Study,New Testament, Church,Matthew,Mark, Luke, John,Heart, Soul, Body,Mind,Spirit,Church History, Books, Pastorso, Evangelists. Teachers, Apostles, Healing, Leadership, Grace, Salvation, Faith,Lifestyle and Entertainment,

%d bloggers like this: