— Assign store procedure execute permissions to user

— script to give Read and Write permissions to user for particular database

— T-SQL  command to assign db_datareader and db_datawriter roles to a user in SQL Server?

Sol: Here The user name is MYUSER and the database is TestDB.

Assign execute Stored Procedures to an user for a Database:

GRANT EXECUTE TO MYUSER;

To assign the ability to execute specific stored procedures:

GRANT EXECUTE ON 'dbo.sp_mystoredprocedure' TO MYUSER;

In SQL Server 2012, 2014:

USE TestDB
GO

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

In SQL Server 2008:

use TestDB
go

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

 

 

 

 

 

 

 

 

 

 

 

 

 

http://stackoverflow.com/questions/456346/what-is-the-t-sql-to-grant-read-and-write-access-to-tables-in-a-database-in-sql

 

Advertisements