Home / server / Questions / 47279
In Process
Geo
Asked: 2009-07-29 14:05:29 +0800 CST

How to bring DB Engine Security together with Database Security MSSQL 2005 Express?

  • 772

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?

windows sql-server sql-server-2005 windows-server-2003

3 Answers

  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!

    • 1

  2. 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.

    • 0

  3. 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.

    • 0