August 2, 2011 at 7:35 pm
I had database on production server (sql server 2008r2). How can i give a user all permissions to perform operations on that database with out affecting the database data. I mean can we implement database mirroring so that he can access mirrored database with out touching principal database, i guess we can not access mirrored database. If we implement transaction log shipping, can we give him the permissions he need on secondary server?what is the solution for this so that user can play on that database?
August 2, 2011 at 8:02 pm
Follow this link
http://msdn.microsoft.com/en-us/library/bb510589.aspx
Scroll down to:
Identity and Access Control
Database-Level Roles - click on the line to access the roles available ..
you would want to assign the user to the db_denydatawriter
August 2, 2011 at 9:55 pm
not only read permissions but he should be able to write.
I guess replication and transaction log shipping are options but which one is best? can i give all permissions to the user for mirroring database in both options? which one is easy to implement? Please reply
August 2, 2011 at 11:12 pm
I came to know only transactional replication is best suited as log shipping has delay of getting data to secondary instance.my doubt is can we create user and give him the write permissions to the database which is in secondary instance so that whatever he make changes that will be done to secondary instance database only?please clarify
August 3, 2011 at 7:32 am
Replication is NOT the solution.
Now before going further, why would you want a user to NOT be able to write to the "original" DB, and yet allow that very same user to write to the secondary instance?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply