0

I am looking at script that will grant db_reader & db_writer roles on a table for a service account in SQL Server 2008 R2. I am looking at granting these permissions only to specific databases. The same service account will have db_reader permissions on one table while db_writer on others

Tom
  • 8,175
  • 41
  • 136
  • 267
  • I am looking at database level and not table level – Tom Aug 17 '17 at 09:14
  • I tried this but it getting syntax error in the To section. GRANT SELECT ON CoreReferenceStaging TO 'INT\svc-w-corerefdata-de' – Tom Aug 17 '17 at 09:20
  • I thought you where looking for a way to assign `db_datareader` and `db_datawriter` roles to a user. If that is the case, then it is a duplicate. If not, your question is not very clear. – Zohar Peled Aug 17 '17 at 10:04
  • I am looking at applying to user for a specific database. so assume you have service account xyz. I need to give xyz db_reader permission on database A and db_writer permission on database d. Cant explain beyond that – Tom Aug 17 '17 at 10:09
  • Please let me know where in that article shows the solution for my problem – Tom Aug 17 '17 at 10:10
  • Did you try `sp_addrolemember` as mentioned in the accepted answer? – Zohar Peled Aug 17 '17 at 10:13
  • That will grant the role for all the databases on the server for a user. – Tom Aug 17 '17 at 11:27
  • No, it wont. db_datareader and db_datawriter are [database level roles](https://technet.microsoft.com/en-us/library/ms189121(v=sql.90).aspx). – Zohar Peled Aug 17 '17 at 11:44
  • so how do you grant db_datareader for a specific database. sp_addrolemember db_datareader grants role to user for all databases – Tom Aug 17 '17 at 12:44
  • No, it will not. It will only grant db_datareader to the for database that you where connected to when you executed the stored procedure! Try it on a test database and see for yourself! – Zohar Peled Aug 17 '17 at 12:53

0 Answers0