什么是T-SQL来授予对SQL服务器中的数据库中的表的读写访问权限?

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

将db_datareader和db_datawriter角色分配给SQL服务器中的用户的确切SQL是什么?

The user name is MYUSER and the database is MYDB.

用户名是MYUSER，数据库是MYDB。

3 个解决方案

#1

In SQL Server 2012, 2014:

在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:

在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;

#2

From SQLServer 2012 more elegant alter role:

来自SQLServer 2012的更优雅的alter角色:

use mydb
go

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

#3

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.

最好创建一个新的角色，然后授予execute, select…etc对这个角色的权限，最后将用户分配给这个角色。

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

要添加用户数据库>安全> >的> databaseroles>属性>添加(右下角)你可以搜索广告用户，然后添加。

或

   EXEC sp_addrolemember 'db_SomeExecutor', 'domainName\UserName'

Please refer this post

请参考这篇文章

#1