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
.
This question is related to
sql-server
tsql
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
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
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
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
Source: Stackoverflow.com