3

When we restore a backup created with the command below we get all the security settings on the database level but the users on the engine level are not created. 

osql -s myserver -E -Q " BACKUP DATABASE MyDB TO DISK = 'C:\SQLBackups\MyDB.bak'"

The main use of this user on the engine level is for connection purposes from the outside (e.g. IIS Application).

databaseName1 mapped to user databaseUser1

We want to know what backup to the original database engine we have to do to bring the connection users, with all their passwords and mappings. Any ideas?

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
Geo
  • 3,071
  • 11
  • 42
  • 52

3 Answers3

1

Database users are different to SQL Server logins. Database users are restored as part of the database, but database server logins are not. The problem your running into is often called orphaned users.

You have 2 options:

  1. Use sp_change_users_login 'Report' to see what users are orphaned, then use 'auto_fix' to create a login on the database server. You'll need to do this everytime you restore the database.

  2. Use sp_help_revlogin to copy just the orphaned logins to the database you've performed the restore on. this is a little more involved, but if its a database you restore regularly then its worth the effort as you'll only have to do this ONCE.

restoring master isn't a real option, unless your restoring ALL databases to a new server, it can get messy!

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
0

To restore the logins, you'd also have to backup & restore the master database as well. If you didn't want to do that, you could just create new logins for the orphaned users in your database using sp_change_users_login.

Scott Ivey
  • 617
  • 1
  • 8
  • 21
  • Hello Scott: Is there any reason why bringing the master database is not a good idea? Thanks for the lead. – Geo Jul 30 '09 at 20:22
  • Hey Scott: "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead." This quoted text is the first line of the link you sent me. Any reason why not using the Alter user? – Geo Jul 30 '09 at 20:28
  • 1
    Restoring the master database is a much more involved process than just your user databases. An alternative might be to find a 3rd party product that can script out the logins - I believe Idera has one of those. Also, for sp_change_users_login being deprecated - i'm not aware of a way to use the "AUTO_FIX" or "REPORT" option with alter user - so I'll still use that stored proc until they have a good replacement for those items. – Scott Ivey Jul 31 '09 at 13:07
0

With SQL 2005, MS introduced sp_help_revlogin. The result set is a script of logins and their passwords (hashed) that is great for moving logins (especially SQL logins) without having to move master.

jl.
  • 1,076
  • 8
  • 10
  • Actually, this was around before SQL Server 2005. See the KB article which covers copying logins from one SQL Server to another (linked by Nick Kavadias). – K. Brian Kelley Aug 10 '09 at 17:19