1

I am currently granting permissions to a service account on a database. I want to ensure if the granting db_datareader and db_datawriter database roles is the same as my grant command.

Adding service account to db_datareader and db_datawriter database roles.

EXEC sp_addrolemember N'DB_DATAREADER', N'INT\svc-w-corerefdata-de';
EXEC sp_addrolemember N'DB_DATAWRITER', N'INT\svc-w-corerefdata-de';

Creating a role, granting permissions to it and adding the service account to the role.

IF DATABASE_PRINCIPAL_ID('AlterPermCoreRefData') IS NULL
    BEGIN 
        CREATE ROLE [AlterPermCoreRefData] AUTHORIZATION [dbo]
    END

GRANT EXECUTE, ALTER, SELECT, DELETE, INSERT, UPDATE ON database::[CoreReferenceStaging] TO [AlterPermCoreRefData]

EXEC sp_addrolemember 'AlterPermCoreRefData', 'INT\svc-w-corerefdata-de'
  • Neither db_datareader nor db_datawriter includes the ALTER or EXECUTE permissions: docs.microsoft.com/en-us/sql/relational-databases/security/… – Gareth Lyons Dec 8 '17 at 16:39
  • I basically need the service account to create tables in the database, insert records, truncate records, update records, execute storeprocedures and be able to connect to the database. What is the best and minimum way to achieve that – Tom Dec 8 '17 at 17:08
4

Have a look at the fixed db roles in SQL server:

In your first example (db_datareader and db_datawriter), the service account can only read and modify data in the tables. With these 2 roles, the service account cannot modify the tables or perform and DDL. Nor does it have EXECUTE permissions.

In your second example, you've given all the permissions above but you've added EXECUTE and ALTER. This allows that account to execute SPs and also modify objects (structurally, or otherwise) within the DB.

So, to answer your question, no, they are not the same. The second example has more permissions and can do more.

  • I basically need the service account to create tables in the database, insert records, truncate records,, update records, execute storeprocedures and be able to connect to the database. What is the best and minimum way to achieve that – Tom Dec 8 '17 at 17:07
  • The second option – Kris Gruttemeyer Dec 8 '17 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.