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.
What is the T-SQL To grant read and write access to tables in a database in SQL Server?
Viewed 116550
3 Answers
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;
Related