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’
BEGINSELECT @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 @UserStringEND
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 #ModifySchemaTempEND
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