What is the T-SQL To grant read and write access to tables in a database in SQL Server?

Question

What is the exact SQL to assign db_datareader and db_datawriter roles to a user in SQL Server?

The user name is MYUSER and the database is MYDB.

score 121 · Accepted Answer · edited Apr 19 '16 at 10:11

In SQL Server 2012, 2014:

USE mydb
GO

ALTER ROLE db_datareader ADD MEMBER MYUSER
GO
ALTER ROLE db_datawriter ADD MEMBER MYUSER
GO

In SQL Server 2008:

use mydb
go

exec sp_addrolemember db_datareader, MYUSER 
go
exec sp_addrolemember db_datawriter, MYUSER 
go

To also assign the ability to execute all Stored Procedures for a Database:

GRANT EXECUTE TO MYUSER;

To assign the ability to execute specific stored procedures:

GRANT EXECUTE ON dbo.sp_mystoredprocedure TO MYUSER;

score 12 · Answer 2 · answered Dec 18 '13 at 14:40

12

From SQLServer 2012 more elegant alter role:

use mydb
go

ALTER ROLE db_datareader
  ADD MEMBER MYUSER 
go
ALTER ROLE db_datawriter
  ADD MEMBER MYUSER 
go

answered Dec 18 '13 at 14:40

dani herrera

score 2 · Answer 3 · edited May 23 '17 at 12:10

It will be better to Create a New role, then grant execute, select ... etc permissions to this role and finally assign users to this role.

Create role

CREATE ROLE [db_SomeExecutor] 
GO

Grant Permission to this role

GRANT EXECUTE TO db_SomeExecutor
GRANT INSERT  TO db_SomeExecutor

to Add users database>security> > roles > databaseroles>Properties > Add ( bottom right ) you can search AD users and add then

OR

   EXEC sp_addrolemember 'db_SomeExecutor', 'domainName\UserName'

Please refer this post

3 Answers3