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

时间:2021-11-12 09:13:25

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


49  

This is a two-step process:

这是一个两步过程:

  1. 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;
    
  2. 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


49  

This is a two-step process:

这是一个两步过程:

  1. 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;
    
  2. 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)'