如何授予用户对SQL服务器中的数据库的读访问权限?

I want to grant access to a user to a specific database with read and write access. The user is already available in the domain but not in the DB.

我想授予用户对具有读写访问权限的特定数据库的访问权限。用户已经在域中可用，但在DB中不可用。

So, how can I give them that access with creating a new user and password?

那么，我如何让他们通过创建新用户和密码进行访问呢?

Someone told me that it can be done with only specifying the user, domain & the DB that you want to give the user the access to without needing to create a new user and password.

有人告诉我，只需要指定用户、域和要为用户提供访问权限的DB就可以完成，而不需要创建新的用户和密码。

This is the old way that I was implementing. It works but it creates a new login and user rather than using the one that is available in the domain:

这是我执行的旧方法。它可以工作，但它创建了一个新的登录和用户，而不是使用域中可用的那个:

use DBName;
create login a_2 with password='Aa123';
create user a_2 for login a_2;
grant insert to a_2;
grant select to a_2;

1 个解决方案

#1

This is a two-step process:

这是一个两步过程:

you need to create a login to SQL Server for that user, based on its Windows account

您需要根据其Windows帐户为该用户创建一个登录到SQL Server。
```
CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;
```
you need to grant this login permission to access a database:

您需要授予此登录权限才能访问数据库:
```
USE (your database)
CREATE USER (username) FOR LOGIN (your login name)
```

Once you have that user in your database, you can give it any rights you want, e.g. you could assign it the db_datareader database role to read all tables.

一旦您的数据库中有了该用户，您就可以给它任何您想要的权限，例如，您可以为它分配db_datareader数据库角色来读取所有的表。

USE (your database)
EXEC sp_addrolemember 'db_datareader', '(your user name)'

#1