Find database Schema Users

By Joseph Kravis on February 26, 2017 • ( 0 )

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

— 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