Find database Schema Users

I’ve imported and migrated a tremendous amount of databases into SQLServer versions 2012 and above. Finding and identifying old owners as they would be orphans now or users that should not have access can be made easier by running or fully automating an import workflow by adding this script to the process.

It is pretty basic in structure and uses #TempTable rather than @TempTable.

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’

I select from sys.database_role_members and self join with database_principlas against databse_principals selecting user type “U” into my #ModifySchemaTemp table.
A simple WHILE loop provides the core of the script.  For the amount of data I choose not to use CURSORS.

SELECT @cnt=COUNT(*) from #ModifySchemaTemp

WHILE(@cnt >0)
BEGIN
–Set Loop While Keys
SELECT @UserKey=UserName from #ModifySchemaTemp
SELECT @SchemaKey=UserSchema from #ModifySchemaTemp

–REST OF PROCESSING

END

Modify the script to suit your needs. As you see from the script rather than take action immediately which could be done I’ve choose to print to the screen for later documentation.

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’

— Check results if you want uncomment the following
–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



Categories: TSQL

Tags: , ,

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: