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.

Example database: WarehouseDB
Example user: reporting
Database state: Read only/Standby mode

How do I give the user 'reporting' access to only WarehouseDB without giving them sysadmin privileges?

The problem occurred when I restored a bak file to create WarehouseDB in norecovery mode and then restored another transaction log file to put it in read only/standby mode for doing select queries for reporting purposes. And the reason I do log shipping then onwards is because both production and warehouse database can be synced at all times without any manual intervention. Now if I give this user sysadmin permissions then of course they can query the database but due to security concerns, I can't allow them. Also because it's a read only database, I can't create another user either and map them to WarehouseDB.

Another thing I forgot to mention is, the source database name is for example 'ProductionDB' and when restoring it's changed to WarehouseDB because I have mirroring setup for ProductionDB on the mirrored server where WarehouseDB is then restored.

Any ideas on how to resolve this?

share|improve this question
    
SQL Server login, or Windows login? –  Jon Seigel Jan 17 '13 at 18:57
    
It's a SQL login. –  avakharia Jan 17 '13 at 18:59
    
All you need to do is fix the login SID on the secondary server. See my edit. –  Jon Seigel Jan 17 '13 at 19:00
add comment

1 Answer 1

up vote 2 down vote accepted

The user has to be granted permissions in the source copy of the database.

The restored copy is just that -- a copy -- of the source, so there's no way to change one and not the other.

Since this is a SQL Server login, you'll need to create the logins on both servers with identical SIDs so the mapping from the database user to the login matches in all cases. You can do this by recreating the login on the secondary, specifying the SID manually in the CREATE LOGIN statement.

If you went with a solution such as replication (not saying you should, just giving an example), this works using two read/write databases, where you could grant permissions completely independently.

share|improve this answer
    
The user did exist in the source database but lost mapping when restored. Just updated the question (see last paragraph). –  avakharia Jan 17 '13 at 18:56
    
I'll try the SID method tonight and let you know how it goes. –  avakharia Jan 17 '13 at 20:00
    
If you want security you can go a step further and remove the login from the primary server (just leave the user intact). That way you have the user in the database but no login on the server, thus securing your primary. Create the login on the secondary (specifying the SID from the primary) and you'll have permisions on the secondary without granting access to the primary. –  Nic Jan 17 '13 at 23:19
    
Thanks guys for the tip. It helped and it works now. –  avakharia Jan 18 '13 at 13:28
    
@avakharia: You're welcome! Glad you got it fixed. –  Jon Seigel Jan 18 '13 at 14:30
add comment

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.