0

Whenever I restore a database all my logins associated with that database are removed. How can I avoid this ?

3

Logins reside in master database and users reside in non system databases. Logins does not come with user database backup. There is an exception for contained database, in which case there is no login mapped to user. Everything you need is contained in the user database as mentioned by @Nic and @Sepupic in comment section.

Read this for more details:

Basic SQL Server Security concepts – logins, users, and principals by Laurentiu Cristofor

Follow the process described here to copy logins to destination server.

How to transfer logins and passwords between instances of SQL Server

You can also use PowerShell function to copy logins.

COPY-DBALOGIN

  • This is the correct information. Just for completeness, in the case of a Contained database the login is stored in the database. – Nic Nov 21 '17 at 16:08
  • Thank you @Nic. Valid point. Updating my answer. – SqlWorldWide Nov 21 '17 at 16:09
  • In case of contained database, logins do not exist at all.Database authenticates USERS, that are created with the comand CREATE USER, and stroes them as users, in sys.database_principals, see docs.microsoft.com/en-us/sql/relational-databases/security/… – sepupic Nov 21 '17 at 17:52
0

You'll most likely need to script out the creation of the users, and then run that script after you restore the database, as that information isn't inside the database backup.

  • is there any script that you can think of.thanks – SqlNovice Nov 21 '17 at 15:29
  • Not that I know of, but you would be able to script the users out in SSMS by right clicking and script as. It won't get everything, including actual permissions. – The Great Laughter Nov 21 '17 at 15:35
  • in that case what is the solution because if i restore a database then all the mapped users will be gone and if we have many users associated with a particular database then it is a lengthy process – SqlNovice Nov 21 '17 at 15:45
  • @SqlNovice where does this backup come from? Why aren't the users created there already? – Aaron Bertrand Nov 21 '17 at 15:48
  • So here is the problem . Server A Database testdb, sqluser-testlogin. Now when I restore the above database on the same server using a login with sa permission after restoring the database from old back ups i notice that the login 'testdb' is not there. My question is does database restore within the same server removes all the mapped logins ? – SqlNovice Nov 21 '17 at 15:51
0

partially contained databases setup can help in this....but be carefull because it can add vulnerability in case you have an account with DB_OWNER in this database, he will be able to add new accounts without your knowledge.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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