Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
maybe the simpliest way is to istall aditional instance of sql server on the same server? –  Alex_L Mar 29 '12 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 '12 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 '12 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 '12 at 9:26
    
see my answer how to transfer the logins with permissions from source server to destination server. –  Alex_L Mar 29 '12 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).

share|improve this answer
    
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 '12 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 '12 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 '12 at 5:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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