SQL Server 2008 Express授予用户权限 [英] SQL Server 2008 Express Grant User Permission

问题描述

什么是适当的SQL命令(不是通过GUI)将Windows用户帐户添加到SQL Server 2008数据库的权限?即..我要授予某人读取权限，而另一人则具有读取/写入权限.

What is the appropriate SQL commands (not through the GUI) to add a windows user account to permissions on a SQL Server 2008 Database? ie.. I want to give someone read access and another person read/write..

推荐答案

这是一个两步过程:

• 首先，您需要基于该用户的Windows凭据为其创建登录.

CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS

这将设置基本权限，甚至可以连接到您的SQL Server

This sets up the basic permission to even connect to your SQL Server

一旦登录，就可以在每个数据库中创建一个要授予该登录权限的用户:

once you have a login, you can create a user in each database where you want to give that login rights to do something:

CREATE USER Johnnie FOR LOGIN Domain\Johnnie;

要授予对数据库中每个表的读取权限，请将db_datareader角色分配给该用户

to grant read permissions on every table in your database, assign the db_datareader role to that user

sp_addrolemember @rolename = 'db_datareader', @membername = 'Johnnie'

要授予对数据库中每个表的读取和写入权限，请为该用户分配db_datareader和db_datawriter角色

如果您需要对可以读取或写入哪些表的更细粒度的控制，则需要手动管理权限，例如通过创建自己的数据库角色并为这些角色分配授权，然后将用户添加到您创建的那些特定于应用程序的角色中

if you need more fine grained control over what tables can be read from or written to, you need to manage permissions manually, e.g. by creating your own database roles and assigning grants to those roles, and then adding your users to those app-specific roles you created

这篇关于SQL Server 2008 Express授予用户权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！