blocks|key|1432762|text|在SQL+Server+2012中，2014：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1432763|USE+mydb
GO

ALTER+ROLE+db_datareader+ADD+MEMBER+MYUSER
GO
ALTER+ROLE+db_datawriter+ADD+MEMBER+MYUSER
GO|code-block|syntax|javascript|1432764|在SQL+Server+2008中：|1432765|use+mydb
go

exec+sp_addrolemember+db_datareader,+MYUSER+
go
exec+sp_addrolemember+db_datawriter,+MYUSER+
go|1432766|要同时分配执行数据库的所有存储过程的能力：|1432767|GRANT+EXECUTE+TO+MYUSER;|1432768|要分配执行特定存储过程的能力，请执行以下操作：|1432769|GRANT+EXECUTE+ON+dbo.sp_mystoredprocedure+TO+MYUSER;|1432770|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

In SQL Server 2012, 2014:

<pre><code>USE mydb
GO

ALTER ROLE db_datareader ADD MEMBER MYUSER
GO
ALTER ROLE db_datawriter ADD MEMBER MYUSER
GO
</code></pre>

In SQL Server 2008:

<pre><code>use mydb
go

exec sp_addrolemember db_datareader, MYUSER 
go
exec sp_addrolemember db_datawriter, MYUSER 
go
</code></pre>

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

<pre><code>GRANT EXECUTE TO MYUSER;
</code></pre>

To assign the ability to execute specific stored procedures:

<pre><code>GRANT EXECUTE ON dbo.sp_mystoredprocedure TO MYUSER;
</code></pre>

blocks|key|1432789|text|来自SQLServer+2012更优雅的alter+role|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1432790|use+mydb
go

ALTER+ROLE+db_datareader
++ADD+MEMBER+MYUSER+
go
ALTER+ROLE+db_datawriter
++ADD+MEMBER+MYUSER+
go|code-block|syntax|javascript|1432791|entityMap|0|LINK|mutability|MUTABLE|url|http://technet.microsoft.com/en-us/library/ms189775.aspx^0|K|A|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

From SQLServer 2012 more elegant <a href="http://technet.microsoft.com/en-us/library/ms189775.aspx">alter role</a>:

<pre><code>use mydb
go

ALTER ROLE db_datareader
 ADD MEMBER MYUSER 
go
ALTER ROLE db_datawriter
 ADD MEMBER MYUSER 
go
</code></pre>

blocks|key|1647701|text|最好是创建一个新角色，然后授予execute、select+...etc权限授予此角色，并最终将用户分配给此角色。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1647702|创建角色|1647703|CREATE+ROLE+[db_SomeExecutor]+
GO|code-block|syntax|javascript|1647704|授予此角色权限|1647705|GRANT+EXECUTE+TO+db_SomeExecutor
GRANT+INSERT++TO+db_SomeExecutor|1647706|要添加用户database>security>+>+roles+>+databaseroles>Properties+>+Add+(右下角)，您可以搜索AD用户，然后添加|1647707|或|1647708|+++EXEC+sp_addrolemember+'db_SomeExecutor',+'domainName\UserName'|1647709|请参考此post|offset|length|1647710|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/3998634/sql-server-2008-how-do-i-grant-privileges-to-a-username^0|0|0|0|0|0|0|0|0|4|4|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|16|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|17|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|18|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|19|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Q|3|R|5|F|7|1B|8|@]|9|@]|A|$G|H]]|$1|S|3|T|5|6|7|1C|8|@]|9|@$U|1D|V|1E|1|1F]]|A|$]]|$1|W|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]]]

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

<pre><code>CREATE ROLE [db_SomeExecutor] 
GO
</code></pre>

Grant Permission to this role

<pre><code>GRANT EXECUTE TO db_SomeExecutor
GRANT INSERT TO db_SomeExecutor
</code></pre>

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

OR 

<pre><code> EXEC sp_addrolemember 'db_SomeExecutor', 'domainName\UserName'
</code></pre>

Please refer this <a href="https://stackoverflow.com/questions/3998634/sql-server-2008-how-do-i-grant-privileges-to-a-username">post</a>

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

The user name is <code>MYUSER</code> and the database is <code>MYDB</code>.

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

SQL Server

数据库

在SQL Server中为用户分配db_datareader和db_datawriter角色的确切方法是什么？用户名为MYUSER，数据库为MYDB。

问授予对SQL Server数据库中的表的读写访问权限的T-SQL是什么？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问授予对SQL Server数据库中的表的读写访问权限的T-SQL是什么？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问授予对SQL Server数据库中的表的读写访问权限的T-SQL是什么？
EN