4

We are doing a conversion from one system to another which is requiring backup/restores. I am trying to create a process that will remove all users after the restore on the new servers as no user permissions will follow the database to the new server. I have it working for a single database, but when I try and loop through all databases and run my process I have issues. Does anyone have suggestions on how I can enhance my script to get the results I am looking for.

DECLARE @SqlCommand VARCHAR(4000)
DECLARE @MaxRowNum INT
DECLARE @MinRowNum INT
DECLARE @DatabaseCount INT
DECLARE @MaxDatabaseCount INT
DECLARE @DatabaseName VARCHAR(50)

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY Name), [name] INTO #DatabaseList FROM sys.databases WHERE database_id > 4 AND [name] NOT IN ('DBAdmin', 'FS_TEST', 'Garry') ORDER BY [name]
SELECT @DatabaseCount = (SELECT MIN(RowNum) FROM #DatabaseList), @MaxDatabaseCount = (SELECT MAX(RowNum) FROM #DatabaseList), @DatabaseName = DB_NAME()

WHILE @DatabaseCount <= @MaxDatabaseCount
BEGIN
SELECT @SqlCommand = 'USE [' + name + ']' + CHAR(13) + 'GO'FROM #DatabaseList WHERE RowNum = @DatabaseCount 

EXEC (@SqlCommand)

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY Name), [name] INTO #UserList FROM sys.database_principals where (type_desc like '%use%' OR type_desc LIKE '%group%') and name not in('dbo','guest','INFORMATION_SCHEMA','sys','public')
SELECT @MinRowNum = (SELECT MIN(RowNum) FROM #UserList), @MaxRowNum = (SELECT MAX(RowNum) FROM #UserList), @DatabaseName = DB_NAME()

WHILE @MinRowNum <= @MaxRowNum
BEGIN
    SELECT @SqlCommand = 'DROP USER [' + name + ']' FROM #UserList WHERE RowNum = @MinRowNum

    print (@SqlCommand)
    PRINT (@SqlCommand + ' --- User Dropped from ' + @DatabaseName)

    SET @MinRowNum = @MinRowNum + 1
END

SET @DatabaseCount = @DatabaseCount + 1
DROP TABLE #UserList
END 
DROP TABLE #DatabaseList
2
  • Maybe I am not communicating this properly. I am wanting to remove all non-system users from each user database after it is restored on the new server. The logins will not exist and will never exist on the new server to sync them to the restored database. Logins and users will be applied later in the process and will never match the old login/users from the existing system. I simply want to run a process that will loop through each database and drop each user that is not a system user. I appreciate all the suggestions, but some do not apply to my use case. – user761786 Apr 15 '14 at 15:33
  • Take a look at my SQL Server Permissions Manager project, it may be of some use to you. Specifically, it has a removeAllUsersFromDB stored procedure that you can use. Set it up in a central location and all you have to do is call the proc and pass in the database name you want to drop the users from. – Eric Cobb Dec 29 '17 at 13:40
1

I am trying to create a process that will remove all users after the restore on the new servers as no user permissions will follow the database to the new server.

There is no need to delete all the users.

What you can do is use sp_help_revlogin to move all the logins that you are interested or your applicaiton/s need to the new server.

Once you move your database/s to the new server, you even dont have to sync up the users as the logins that you created from above script will be created with the SID from the old server.

As a side note, below script will help you fix the orphaned users:

WITH cte AS
(
SELECT dp.name AS DBUser, 
       dp.sid AS DBSid
  FROM sys.database_principals dp
       LEFT OUTER JOIN sys.server_principals sp 
         ON dp.sid = sp.sid 
 WHERE sp.sid IS NULL 
   AND dp.type = 'S'  -- SQL_USER
   AND dp.principal_id > 4
)
-- join to logins with the same name to get the sql statement to un-orphan the user
SELECT cte.*, 
       sp.name, 
       -- generate the SQL script to un-orphan the users that can be.
       LinkOrphanUserToLoginSQL = N'ALTER USER [' + cte.DBUser + N'] WITH LOGIN=' + sp.name + N';'
  FROM cte
       -- LEFT JOIN allows seeing all orphaned users
       LEFT JOIN sys.server_principals sp  
         ON cte.DBUser = sp.name
        AND sp.type = 'S'
ORDER BY DBUser;
0

It would be really helpful if you said what your issues were.

One thing I can see immediately is a scope problem. EXEC() creates its own scope. Your statement to USE a database will use that database only until the EXEC() returns. Then you are back into whatever database the above-posted code is running in. Let's say "MyDB" for the sake of argument. The DROP USER statement will form its own scope again (I'm assumuning you EXEC() this in your real code and there's a typo in the pasted example), inheriting MyDB as the context since you haven't told it to do anything else. Nett result is MyDB is really clean and no other DB gets touched!

Fix: include a USE statement in every EXEC().

Have a good read of Books Online. If took me a while to get my head around it but all the details are in there.

2
  • The issue is that it looks through the databases, but the command to drop users continues to run against 'master' and not each database as I loop. I just added the 'USE' to each execute and that did not help. – user761786 Apr 15 '14 at 13:39
  • @DatabaseName = DB_NAME() on the row beginning SELECT @MinRowNum. I bet you are using this variable in your new USE statements. – Michael Green Apr 15 '14 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.