Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ;-)

share|improve this question
add comment

5 Answers

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.

http://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.

share|improve this answer
    
I'm sure this wont' be the first time I tussle with SQL Server over users/permissions so i'll bookmark the article for posterity. Many thanks for posting. –  5arx Mar 23 '11 at 16:49
add comment

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')
share|improve this answer
    
Thanks for your response - my web app uses SQL logins while our windows accounts are used for administration purposes. I can't see a way to restore as 'loginsource' as you suggest - could you please elaborate? I'm hoping this can all be done using Managment Studio? –  5arx Mar 23 '11 at 14:57
    
PS. I followed this doc: support.microsoft.com/kb/274188 and ran all the scripts (apparently) successfully. But nothing has changed. –  5arx Mar 23 '11 at 15:20
add comment

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.

share|improve this answer
    
Thanks for the script. Can I assume there's no way of doing this in Management Studio? Also, some people are suggesting I delete and recreate the DB-level users - is this recommended? –  5arx Mar 23 '11 at 15:35
    
This assumes logins already exists and only mataches SIDs –  gbn Mar 23 '11 at 15:36
    
doesn't sp_change_users_login update the SID? msdn.microsoft.com/en-us/library/ms174378.aspx –  SQLRockstar Mar 23 '11 at 15:40
    
5arx - there is no way to do this in SSMS. As i said, in an ideal situation you would have scripted your permissions before the restore. At this point you either need to recreate by hand or try to manually piece things together yourself. if i were you, i would restore the old db, script out the permissions, do the restore from the other server, and then redo your permissions. –  SQLRockstar Mar 23 '11 at 15:42
    
"Update_One: Links the specified user in the current database to an existing SQL Server login. user and login must be specified. password must be NULL or not specified." So it assumes logins exists already. –  gbn Mar 23 '11 at 20:08
show 1 more comment

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

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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