I am having following scenarios and i am having some issues and some questions.

  1. We have a DB Server on which we have some databases in these 2(two databases) are for my users.
  2. Now i have created 2 windows group login A & B, A is the user which can only read data from 2 DB's, and B is the user which can do a restore to DB's.
  3. Now what i like to know, is this possible to restrict user B to have restore DB rights only on 2 databases(i have many on my server)
  4. Another problem i am facing is when i am restoring DB from user A than all rights assigned to user B from that specific DB are not there, so how can i always keep my user rights same for every DB.

I am using SQL Server 2008. Thanks in Advance.

  • maybe the simpliest way is to istall aditional instance of sql server on the same server?
    – Alex_L
    Mar 29, 2012 at 5:44
  • @Alex_L this is not a solution of problem, i like to get the proper way to handle this kind of problem.
    – Ashish
    Mar 29, 2012 at 8:06
  • In this case I cannot see the appropriate solution, because the permissions for restore are granted on entire server level. if you give the maximum database-level permissions (I mean DBO) without server-level permissions, this DBO will not have the restore ability.
    – Alex_L
    Mar 29, 2012 at 8:10
  • @Alex_L ok.... what about users and login.. and their rights on server. as i am saying that when i restore the DB from user B all rights assign to user A.. removed by itself... how i can keep the rights intact..??
    – Ashish
    Mar 29, 2012 at 9:26
  • see my answer how to transfer the logins with permissions from source server to destination server.
    – Alex_L
    Mar 29, 2012 at 9:40

1 Answer 1


To move logins and users from the source server, I use the following solution. This script helps completelly transfer logins with user permissions (see description on the top of the script how t use it and to specify the databases and logins).

  • what i think is the logins are not the issue here... when i restore database on my machine than login B is already on SQL Server.. what i think is that database doesn't have the USER associated with that login that is why am having this problem
    – Ashish
    Mar 30, 2012 at 5:09
  • logins SIDs on the servers are different, so after database restore database users cannot be mapped to logins on new server. These users called "orphaned". There is two ways to fix the orphaned users: re-map the users to existing logins (see the solution here sqlteam.com/forums/topic.asp?TOPIC_ID=12615 ) or recreate the logins from "old" server to the "new" server with "old" SIDs as I proposed in my answer. It is possible, that first solution (sp_fixusers) is more applicable for you.
    – Alex_L
    Mar 30, 2012 at 5:23
  • thanks for the link... I like to know what exactly this will do... sp_change_users_login @action='Auto_Fix', 'USERNAME'
    – Ashish
    Mar 30, 2012 at 5:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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