Restore Database - User Access - SQL Server 2005

[Kiwiman]

Hi

We are implementing a new Billing system, with part of the process being cleansing our existing contracts. This cleansing is done on our Live system. Every two weeks or so, I restore the Development Box (also SQL Server 2005) with a copy of Dev.

Prior to the restore the user access to this database is exactly the same as in Live.

After the restore, the users still show as having access to this database via:

• Databases
• Select Database Restored
• Security
• Users

However, under the below the Users access is no longer ticked for the restored database (this was definitley ticked prior to the restore).

• Security
• Logins
• Select User - RT Click Properties
• User Mapping

When the user tries to log in after the restore, access is denied, probably because of the difference in the datbase security and the overall SQL Server security.

Could someone advise how this can be rectified, and point in me in the right direction as to the steps involved when restoring a database that will not cause this to occur.

Any help is much appreciated.

 

[SQL_Hell]

I suspect what's happening is your user accounts and logins have become
unsychronised on the DEV server after the restore.

The logins on a server each have their own primary key called a SID, these SIDS also are a foreign key for the users table in each database.
Your logins on your DEV server have different SIDS from the logins on the production server.

There is a system stored procedure that enables you to fix this issue, it's called "sp_change_users_login".
Take a user account and login that you know deosn't work after the restore (seeing as I don't know your users account I am going to say the login is Bob and the user is also called Bob), run the following:

exec sp_change_users_login 'update_one','Bob','Bob'

If you are using windows logins then make sure you have the domain prefixed for the loginname e.g

exec sp_change_users_login 'update_one','Bob','UK\Bob'

and if you have have SQL server login then you need to supply the password as the last parameter.

Give it a try with one of your failed logins and see if it fixes the issue

Are you restoring using the GUI?

 

[Kiwiman]

Howzit

Thanks for the clear explanation, that is almost certainly happening.

I'll give it a whirl and let you know how I get on.

I don't normally do this, just shown by our IT crowd how to restore a database - they didn't mention anything about the logins getting unsynchronised. Can't currently get a hold of them to resolve for me - hence the post here.

Are you restoring using the GUI?

Not sure what you mean by this. Restore down by RT CLick on database \ Tasks \ Restore \ Database in SQL Server Management Studio.

 

[Kiwiman]

Howzit

Thanks - that did the trick.

Will remember this for the future.

 

[Kiwiman]

Howzit

Further to the earlier posts our 3rd party GP support sent this on to us to run. A colleague ran it and advised that not all worked - so I am investigating which ones didn't work. First of all I would like to work out what each part of this script does.

Can someone please explain:

1. What the open User_Curser part is - is it way to loop throught he recordset?
2. What the while @@fetch_status = 0 does - where does the @@fetch_statsus come from
3. what do the Print commands do - i.e. print @username and print 'User ' + @username + ' found in master.dbo.sysxlogins'

/* fix users in database - use this version if 'AUTO_FIX' doesn't work
   this can be due to service pack 3 having been applied               */

declare user_cursor cursor for select name from sysusers where status = 2 order by name
declare @username varchar(40)
open user_cursor
fetch next from user_cursor into @username
while @@fetch_status = 0 
begin
  print @username
  if @username = 'dbo'
    print 'user dbo skipped'
  else
    begin
      if exists (select name from master.dbo.sysxlogins where name = @username)
        begin
          print 'User ' + @username + ' found in master.dbo.sysxlogins'
        end
      else
        begin
          print 'User ' + @username + ' NOT found in master.dbo.sysxlogins - creating'
          exec sp_addlogin @username
        end
      print 'Linking Database user ' + @username + ' to security login ' + @username
      exec sp_change_users_login 'Update_One', @username, @username
    end
  fetch next from user_cursor into @username
end
close user_cursor
deallocate user_cursor

As always, I thank you for your help in this.

 

[SQL_Hell]

Hmm disappearing replies again... I did answer these cursor questions.

Kiwiman: do you still need the answers to the questions above?

 

[Kiwiman]

Howzit

Hmm disappearing replies again... I did answer these cursor questions.

Kiwiman: do you still need the answers to the questions above?

Yes - if you don't mind. I like to try and understand something before \ or instead of just blindly running things.

Thanks again for your help.

 

[SQL_Hell]

1. Yes 'open cursor' opens a cursor for looping through recordsets.

2. @@fetch_status is a system parameter that keeps the cursor looping, it appears in all cursors and is just part of the syntax.

3. the print command outputs a line of text to the message result box in the query pane.
Try it with:

Print 'hello world'

Cursors aren't exactly very efficient so do not use them for looping through large recordsets, but in this instance where you are only looping through users and logins then it is fine.