User Permissions Messed Up following a Backup -> Restore Operation

Question

I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management Studio).

All went OK but now I find I can't login to any of the databases using the SQL Server account that continues to work on the old RDBMS. My windows-authenticated login still works fine incidentally.

I had this idea the users and permissions would all be seamlessly duplicated onto the new database server but it seems something has gone wrong somewhere. I would appreciate comments/suggests/offers of help ;-)

Answer 1

I see you already found a solution to your problem, one thing I noticed in your original question was that you still had access to the old server.

The following question on SO had a similar problem and includes links to a Microsoft article with a script to generate the user permissions.

https://stackoverflow.com/questions/461385/restoring-a-backup-to-a-different-server-user-permissions

(Resource listed for that question http://support.microsoft.com/kb/918992)

It sounds like changing the server setting from Windows authentication to Mixed mode authentication fixed your problem, but just in case it didn't completely resolve the problem I thought this might be useful.

Answer 2

This is known as "orphaned users". Here are 2 ways to fix it

1. If you can, restore the original master database as "loginsource" and sys.server_principals has enough info to generate all SQL Server and Windows logins. That is, the SIDs and encrypted password

2. If you use Windows logins only, then you can run this per database to generate a script

Script:

SELECT
    'CREATE LOGIN [' + SUSER_SNAME(sid) + '] FROM WINDOWS'
FROM
    sys.database_principals
WHERE
    [type] IN ('G', 'U')

Answer 3

Ideally you would script out the users and permissions prior to doing the restore. If that has not happened, then you need to go about fixing things after the fact, and chances are something will be missed but you should be able to get about 90% of the way there.

The first thing you need to ascertain is if the same logins exist on the new server. If they don't then you should find out if it is OK for the logins to be created on the new server. Never assume that they should be created, there could be a good reason as to why they did not exist in the first place. You can then go about creating them by digging through the sysusers table.

You can fix the orphaned users by running something similar to the following:

DECLARE @username varchar(25), @loginsid varbinary(85)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name 
    FROM sysusers
    WHERE issqluser = 1 
    and (sid is not null and sid <> 0x0)
    and suser_sname(sid) is null
    and name in (select name from master..syslogins)
    ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_change_users_login 'update_one', @username, @username

    FETCH NEXT FROM fixusers
    INTO @username
END CLOSE fixusers
DEALLOCATE fixusers 

This code will work for SQL2008, but was written to be backward compatible for SQL2000.

Answer 4

You can refer the following URL to fix the database user permissions

http://mywindowsblog.com/?p=287

Answer 5

Here is a quick article that explains the solution:

Restoring Orphaned Logins in SQL Server After Restoring Database