August 8, 2012 at 10:04 am
Hi All,
Could someone please help me with this. I've setup logshippping with the secondary database as read-only/standby. This read-only database is being used to run reports and stuff.
I've given one user (domain user) access to this server to run queries. As the database is read only I suppose all users will have read only access. But the user is getting this error message.
The SELECT permission was denied on the object 'xxxx, database xxxx', schema 'dbo'
Earlier he had issues viewing the objects. So I granted VIEW ANY definition to his user account in the properties, now how can I grant him SELECT. Shouldn't he be having select permission by default as the database is read only?
Please help
August 8, 2012 at 10:20 am
Users don't get any permissions by default. The users should be granted permissions. When you have a secondary database from log shipping, the database cannot be modified, so you can't change user's permissions. You'll need to grant the user permission on the primary database and then it will be passed to the secondary database. If this is a windows login (e.g. you are using windows authentication), then it will be very simple. If this is for SQL Server login (e.g. you are using SQL Server authentication), you'll might have problems with the mapping between the user and the login. In that case you'll need to make sure that the login has the same SID on both servers. If this is your situation, you can have a look at http://support.microsoft.com/kb/918992%5B/URL%5D to see how to transfer logins so they'll have the same SID.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 8, 2012 at 10:26 am
Thanks for your reply. Yes the user is windows authenticated. So if I just grant read-only access on primary it should replicate after the log ship right?
August 8, 2012 at 10:54 am
Yes. When you'll grant him the permission, it will be written in the log and when that log will be restored at the secondary database, the user will be able to read the data on the secondery database also.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply