2

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?

Improve this question
3
  • SQL Server login, or Windows login?
    – Jon Seigel
    Commented Jan 17, 2013 at 18:57
  • It's a SQL login.
    – avakharia
    Commented Jan 17, 2013 at 18:59
  • All you need to do is fix the login SID on the secondary server. See my edit.
    – Jon Seigel
    Commented Jan 17, 2013 at 19:00

1 Answer 1

Reset to default
3

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.

Improve this answer
4
  • The user did exist in the source database but lost mapping when restored. Just updated the question (see last paragraph).
    – avakharia
    Commented Jan 17, 2013 at 18:56
  • I'll try the SID method tonight and let you know how it goes.
    – avakharia
    Commented Jan 17, 2013 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
    Commented Jan 17, 2013 at 23:19
  • @avakharia: You're welcome! Glad you got it fixed.
    – Jon Seigel
    Commented Jan 18, 2013 at 14:30

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.