当前位置：首页 > 服务器 > 正文

sql创建服务器角色（SQL创建角色）

admin
服务器
2022-12-19 19:28:23
18

本篇文章给大家谈谈sql创建服务器角色，以及SQL创建角色对应的知识点，希望对各位有所帮助，不要忘了收藏本站喔。

本文目录一览：

1、怎样设置 sql 2005 服务器角色数据库
2、sql server中利用sql语句如何创建角色和用户
3、如何用存储过程及SQL代码创建登录，用户和角色
4、如何在sql server 创建用户
5、在SQL sever2000中怎样设置服务器角色
6、sql 数据库中syadmin固定服务器怎么添加角色

怎样设置 sql 2005 服务器角色数据库

/*服务器角色*/

sysadmin

--在 SQL Server 中进行任何活动。该角色的权限跨越所有其它固定服务器角色。

serveradmin

--配置服务器范围的设置。

setupadmin

--添加和删除链接服务器，并执行某些系统存储过程（如 sp_serveroption）。

securityadmin

--管理服务器登录。

processadmin

--管理在 SQL Server 实例中运行的进程。

dbcreator

--创建和改变数据库。

diskadmin

--管理磁盘文件。

bulkadmin

--执行 BULK INSERT 语句。

/*数据库角色*/

public

public 角色

--public 角色是一个特殊的数据库角色，每个数据库用户都属于它。public 角色：

--捕获数据库中用户的所有默认权限。

--无法将用户、组或角色指派给它，因为默认情况下它们即属于该角色。

--含在每个数据库中，包括 master、msdb、tempdb、model 和所有用户数据库。

--无法除去。

db_owner

--进行所有数据库角色的活动，以及数据库中的其它维护和配置活动。

--该角色的权限跨越所有其它固定数据库角色。

db_accessadmin

--在数据库中添加或删除 Windows NT 4.0 或 Windows 2000 组和用户以及 SQL Server 用户。

db_datareader

--查看来自数据库中所有用户表的全部数据。

db_datawriter

--添加、更改或删除来自数据库中所有用户表的数据

db_ddladmin

--添加、修改或除去数据库中的对象(运行所有 DDL)

db_securityadmin

--管理 SQL Server 2000 数据库角色的角色和成员，并管理数据库中的语句和对象权限

db_backupoperator

--有备份数据库的权限

db_denydatareader

--拒绝选择数据库数据的权限

db_denydatawriter

--拒绝更改数据库数据的权限

sql创建服务器角色（SQL创建角色）

sql server中利用sql语句如何创建角色和用户

示例：

创建角色，用户，权限示例在数据库pubs中创建一个拥有表jobs的所有权限、拥有表titles的SELECT权限的角色p_test随后创建了一个登录a_test，然后在数据库pubs中为登录a_test创建了用户账户m_test同时将用户账户m_t

USE pubs

--创建角色 p_test

EXEC sp_addrole 'p_test'

--授予 p_test 对 jobs 表的所有权限

GRANT ALL ON jobs TO p_test

--授予角色 p_test 对 titles 表的 SELECT 权限

GRANT SELECT ON titles TO p_test

--添加登录 a_test,设置密码为pwd,默认数据库为pubs

EXEC sp_addlogin 'a_test','pwd','pubs'

--为登录 a_test 在数据库 pubs 中添加安全账户 u_test

EXEC sp_grantdbaccess 'a_test','m_test'

--添加 m_test 为角色 p_test 的成员

EXEC sp_addrolemember 'p_test','m_test'

--拒绝安全账户 m_test 对 titles 表的 SELECT 权限

DENY SELECT ON titles TO m_test

/*--完成上述步骤后,用 a_test 登录,可以对jobs表进行所有操作,但无法对titles表查询,虽然角色 p_test 有titles表的select权限,但已经在安全账户中明确拒绝了对titles的select权限,所以a_test无titles表的select权限--*/

--从数据库 pubs 中删除安全账户

EXEC sp_revokedbaccess 'm_test'

--删除登录 a_test

EXEC sp_droplogin 'a_test'

--删除角色 p_test

EXEC sp_droprole 'p_test'

如何用存储过程及SQL代码创建登录，用户和角色

首先理解什么是登录，什么是用户，什么是角色：

登录：SQL Server2005中，有两种创建登录的方式：一种是Windows验证登录；另一种是SQL Server身份验证方式。“登录方式”就好比办公楼上使用不同类型的锁，一旦选择了使用什么类型的锁，就可以开始为每位员工配发钥匙，而这个钥匙就是“登录”。或称“登录名”，但员工只有进入大楼的权利，但却没有权限查看和使用里面的资源（比如档案柜）。如果是sysadmin或securityadmin固定服务器角色的成员，则可以创建两种类型的登录之一：标准登录（比如前面比喻中的金属钥匙）和Windows登录（比如较新型的电子出入卡）。

用户：既然员工拥有了进入大楼的钥匙，就应该拥有适当的管理性访问权，他们需要通过访问其他资源来完成工作。例如，如果打算允许财务部访问账目文件，就需要给他们一把文件柜的钥匙。员工现在有两把钥匙：一把是打开大门的钥匙，一把是打开文件柜的钥匙。

同样，一旦用户已登录到了SQL Server上，就需要允许用户访问数据库。为此，需要创建数据库用户账户，然后给这些用户账户授予权限。一旦这个过程结束，SQL Server用户就拥有了多把钥匙：一把打开大门的钥匙（登录），以及一把打开他们需要访问的每个文件柜（数据库）的钥匙。

角色：会计需要签发公司支票，这个权限可以用两种方式之一来授予。第一，可以给每名会计分别提供一本从同一账户中支取的支票簿，并授予从该支票簿中开支票的权限。在这种情况下，需要设法跟踪当月已经开出的全部支票，否则这种方法可能会造成可怕的后果。完成这项工作的最佳方法是让整个公司账户只使用一本支票簿，并给所有会计授予从这本支票簿中开支票的一个组权限。

在SQL Server中，当几个用户需要访问数据库的权限时，比较容易的方法是将所有的权限作为一个组授给他们，而不是设法分别管理每个用户。

言归正传，先用存储过程实现以上操作：

创建新登录具体语法请参考MSDN文档

--创建一个简单的登录，登录名为：newlogin；登录密码：123456；默认数据库：master，默认数据库也可以不指定。

EXEC sp_addlogin 'newlogin','123456','master'

--创建用户

--创建一个简单的用户，如果不指定用户名，则添加到当前数据库登录名中，如果不指定角色，则该用户默认属于public角色。下为添加newlogin登录名。

EXEC sp_adduser 'newlogin'

--创建一个带用户名的用户，用户可以与登录名相同（同上一种类似），也可以不同，但要设定当前登录名，用户角色可选，默认为public。下为将用户newuser添加到newlogin登录名中。

EXEC sp_adduser 'newlogin','newuser'

--创建角色

EXEC sp_addrole 'newrole'

--下为将用户下为将用户newuser添加到newlogin登录名中。并指定newrole角色。

EXEC sp_adduser 'newlogin','newuser','newrole'

--为角色newrole赋予jobs表的所有权限

GRANT ALL ON jobs TO newrole

--为角色newrole赋予sales表的查、改权限

GRANT SELECT,UPDATE ON sales TO newrole

--禁止角色newrole使用employees表的插入权限

DENY INSERT ON employees TO newrole

另一种创建用户和赋予角色的方式

--为登录newlogin在数据库中添加安全账户newuser

EXEC sp_grantdbaccess 'newlogin,'newuser'

--添加newuser为角色newrole的成员

EXEC sp_addrolemember 'newrole','newuser'

--数据库用户、角色、登录的删除操作

--删除当前数据库用户

EXEC sp_revokedbaccess 'newuser';

--删除数据库登录

EXEC sp_droplogin 'newlogin'

--删除数据库角色

EXEC sp_droprole 'newrole'

--从数据库角色(newrole)中删除用户(newuser)

EXEC sp_droprolemember 'newrole', 'newuser'

--用SQL代码新建登录、用户

--创建带密码的mylogin登录名，MUST_CHANGE 选项需要用户首次连接服务器时更改此密码。

CREATE LOGIN mylogin WITH PASSWORD = '123456' MUST_CHANGE;

--创建映射到凭据的登录名。

--以下示例将创建mylogin登录名。此登录名将映射到mycredential凭据。

CREATE LOGIN mylogin WITH PASSWORD = '123456',

CREDENTIAL = mycredential;

--从Windows 域帐户创建登录名

--如果从Windows 域帐户映射登录名，则登录名必须用方括号([ ]) 括起来。

CREATE LOGIN [jack/xiangzhao] FROM WINDOWS;

--如果指定用户名，则不使用默认登录名作为该数据库用户

CREATE USER myuser FOR LOGIN mylogin

--以下示例将创建用户myuser拥有的数据库角色myrole

CREATE ROLE myrole AUTHORIZATION myuser;

--以下示例将创建db_role固定数据库角色拥有的数据库角色myrole

CREATE ROLE myrole AUTHORIZATION db_role;

如何在sql server 创建用户

第一步：安全性-登录名右键“新建登录名”

第二步：输入你的登录名，选择SQL server身份验证，设置登录密码。取消强制密码过期（看具体设定，一般取消）

第三步：选择左上角服务器角色，勾选中public，如果你需要使他拥有所有的权限勾选（sysadmin）

第四步：选择左上角的用户映射，右边的上部分是勾选有权限操作的数据库，下边是定义设置登陆者的角色身份

第五步：左上角的状态，授予和启用，点击确定用户就创建了。

在SQL sever2000中怎样设置服务器角色

sql server2005安全管理之用户、角色、架构与权限

2008-12-04 16:47

--打开数据库

Use databaseName

--创建角色

create role ProgramerRole

--用于创建表存储过程视图

grant create table,create procedure,create view to ProgramerRole

--execute用以执行存储过程，alter用以创建、修改存储过程和视图，

--并可以新建立表，但不能修改表,但也可以删除表和对表改名了

grant select,insert,execute,alter on schema::dbo to ProgramerRole

--用于允许用户查看显示估计的执行计划(081205)

grant showplan to ProgramerRole

--创建登录账号

--create login username with password='password'

--创建数据库用户

create user username for login username

--将用户TestUser添加到TestRole角色中

exec sp_addrolemember 'ProgramerRole','username '

--执行Sql Server Profiler是服务器级权限，所以在master库中授权

USE master;grant alter trace to auto;

注：据库引擎优化顾问必须由具有系统管理员权限的用户进行初始化。在具有系统管理员权限的用户对数据库引擎优化顾问进行初始化之后，任何是 db_owner 固定数据库角色成员的用户，都可以使用数据库引擎优化顾问来优化他们拥有的数据库上的表。

详优化物理数据库设计

参考资料：

用户架构分离：

数据库架构是一个独立于数据库用户的非重复命名空间。您可以将架构视为对象的容器

主体：

是可以请求 SQL Server 资源的实体

Windows 级别的主体

Windows 域登录名

Windows 本地登录名

SQL Server 级的主体

SQL Server 登录名

数据库级的主体

数据库用户

数据库角色

应用程序角色

安全对象：

安全对象是 SQL Server 数据库引擎授权系统控制对其进行访问的资源

安全对象范围有服务器、数据库和架构

安全对象范围：服务器包含以下安全对象：

端点

登录帐户

数据库

安全对象范围：数据库包含以下安全对象：

用户

角色

应用程序角色

程序集

消息类型

路由

服务

远程服务绑定

全文目录

证书

非对称密钥

对称密钥

约定

架构

安全对象范围：架构包含以下安全对象：

类型

XML 架构集合

对象

对象下面是对象类的成员：

聚合

约束

函数

过程

队列

统计信息

同义词

表

视图

架构：

架构是指包含表、视图、过程等的容器。它位于数据库内部，而数据库位于服务器内部

特定架构中的每个安全对象都必须有唯一的名称。架构中安全对象的完全指定名称包括此安全对象所在的架构的名称。因此，架构也是命名空间

权限：

每个 SQL Server 2005 安全对象都有可以授予主体的关联权限

数据库级别的角色：

固定数据库角色是在数据库级别定义的，并且存在于每个数据库中。db_owner 和 db_securityadmin 数据库角色的成员可以管理固定数据库角色成员身份；但是，只有 db_owner 数据库的成员可以向 db_owner 固定数据库角色中添加成员。

每个数据库用户都属于 public 数据库角色。当尚未对某个用户授予或拒绝对安全对象的特定权限时，则该用户将继承授予该安全对象的 public 角色的权限

服务器级别角色：

固定服务器角色在其作用域内属于服务器范围。固定服务器角色的每个成员都可以向其所属角色添加其他登录名。

GRANT 架构权限：

如何配置用户以创建和管理 SQL Server 代理作业

若要配置用户以创建或执行 Microsoft SQL Server 代理作业，必须先将某个现有 SQL Server 登录名或 msdb 角色添加到 msdb 数据库中的下列 SQL Server 代理固定数据库角色之一：SQLAgentUserRole、SQLAgentReaderRole 或 SQLAgentOperatorRole。

默认情况下，这些数据库角色的成员可以创建各自的作业步骤，这些作业步骤不执行其他作业步骤。如果这些非管理用户要运行那些执行其他作业步骤类型（例如，SSIS 包）的作业，它们需要对代理帐户具有访问权限。sysadmin 固定服务器角色的所有成员都有创建、修改和删除代理帐户的权限

为具体的用户设置具体的访问权限收藏

use 你的库名

--新增用户

exec sp_addlogin 'test' --添加登录

exec sp_grantdbaccess N'test' --使其成为当前数据库的合法用户

exec sp_addrolemember N'db_owner', N'test' --授予对自己数据库的所有权限

--这样创建的用户就只能访问自己的数据库,及数据库中包含了guest用户的公共表

--删除测试用户

exec sp_revokedbaccess N'test' --移除对数据库的访问权限

exec sp_droplogin N'test' --删除登录

如果在企业管理器中创建的话,就用:

企业管理器--安全性--右键登录--新建登录

常规项

--名称中输入用户名

--身份验证方式根据你的需要选择(如果是使用windows身份验证,则要先在操作系统的用户中新建用户)

--默认设置中,选择你新建的用户要访问的数据库名

服务器角色项

这个里面不要选择任何东西

数据库访问项

勾选你创建的用户需要访问的数据库名

数据库角色中允许,勾选"public","db_ownew"

确定,这样建好的用户与上面语句建立的用户一样

---------------------------------------------------------------------------

最后一步,为具体的用户设置具体的访问权限,这个可以参考下面的最简示例:

--添加只允许访问指定表的用户:

exec sp_addlogin '用户名','密码','默认数据库名'

--添加到数据库

exec sp_grantdbaccess '用户名'

--分配整表权限

GRANT SELECT , INSERT , UPDATE , DELETE ON table1 TO [用户名]

--分配权限到具体的列

GRANT SELECT , UPDATE ON table1(id,AA) TO [用户名]

sql 数据库中syadmin固定服务器怎么添加角色

数据库角色的成员可以分为如下几类：

Windows用户组或用户账户

SQL Server登录

其他角色

SQL Server的安全体系结构中包括了几个含有特定隐含权限的角色。除了数据库拥有者创建的角色之外，还有两类预定义的角色。这些可以创建的角色可以分为如下几类：

固定服务器

固定数据库

用户自定义

固定服务器

由于固定服务器是在服务器层次上定义的，因此它们位于从属于数据库服务器的数据库外面。下表列出了所有现有的固定服务器角色。

固定服务器角色

说明

sysadmin

执行SQL Server中的任何动作

serveradmin

配置服务器设置

setupadmin

安装复制和管理扩展过程

securityadmin

管理登录和CREATE DATABASE的权限以及阅读审计

processadmin

管理SQL Server进程

dbcreator

创建和修改数据库

diskadmin

管理磁盘文件

下面两个系统过程用来添加或删除固定服务器角色成员：

sp_addsrvrolemember

sp_dropsrvrolemember

注意：您不能添加、修改或删除固定服务器角色。另外，只有固定服务器角色的成员才能执行上述两个系统过程来从角色中添加或删除登录账户。

sa登录

sa登录是系统管理员的登录。在以前的SQL Server版本中不存在角色，sa登录具有所有可能的关于系统管理工作的权限。在SQL Server 2005中，sa登录保持了向后兼容性。sa登录永远是固定服务器角色syadmin中的成员，并且不能从该角色中删除。

注意：只有当没有其他方法登录到SQL Server系统中时，再使用sa登录。

固定服务器角色及其权限

在某个SQL Server系统中，每个固定服务器角色都有其隐含的权限。使用系统过程sp_srvrolepermission可以浏览每个固定服务器角色的权限。该系统过程的语法形式为：

sp_srvrolepermission[[@srvrolename =] 'role']

如果没有指定role的值，那么所有的固定服务器角色的权限都将显示出来。下面的部分将讨论每个固定服务器角色的权限。

1. sysadmin

固定服务器角色sysadmin的成员被赋予了SQL Server系统中所有可能的权限。例如，只有这个角色中的成员(或一个被这个角色中的成员赋予了CREATE DATABASE权限的用户)才能够创建数据库。

固定服务器角色和sa登录之间有着特殊的关系。sa登录一直都是固定服务器角色中的成员，并且不能从该角色中删除。

2. serveradmin

固定服务器角色serveradmin的成员可以执行如下的动作：

向该服务器角色中添加其他登录

运行dbcc pintable命令(从而使表常驻于主内存中)

运行系统过程sp_configure(以显示或更改系统选项)

运行reconfigure选项(以更新系统过程sp_configure所做的所有改动)

使用shutdown命令关掉数据库服务器

运行系统过程sp_tableoption为用户自定义表设置选项的值

3. setupadmin

固定服务器角色setupadmin中的成员可以执行如下的动作：

向该服务器角色中添加其他登录

添加、删除或配置链接的服务器

执行一些系统过程，如sp_serveroption

4. securityadmin

固定服务器角色securitypadmin中的成员可以执行关于服务器访问和安全的所有动作。这些成员可以进行如下的系统动作：

向该服务器角色中添加其他登录

读取SQL Server的错误日志

运行如下的系统过程：如sp_addlinkedsrvlogin、sp_addlogin、sp_defaultdb、sp_defaultlanguage、sp_denylogin、sp_droplinkedsrvlogin、sp_droplogin、sp_grantlogin、sp_helplogins、sp_remoteoption和sp_revokelogin(所有这些系统过程都与系统安全相关。)

5. processadmin

固定服务器角色processadmin中的成员用来管理SQL Server进程，如中止用户正在运行的查询。这些成员可以进行如下的动作：

向该服务器角色中添加其他登录

执行KILL命令(以取消用户进程)

6. dbcreator

固定服务器角色dbcreator中的成员用来管理与数据库创建和修改有关的所有动作。这些成员可以进行如下的动作：

向该服务器角色中添加其他登录

运行CREATE DATABASE和ALTER DATABASE语句

使用系统过程sp_renamedb来修改数据库的名称

7. diskadmin

固定服务器角色diskadmin的成员可以进行如下与用来存储数据库对象的文件和文件组有关的动作：

向该服务器角色中添加其他登录

运行如下系统过程：sp_ddumpdevice和sp_dropdevice。

运行DISK INIT语句

固定数据库角色

固定数据库角色在数据库层上进行定义，因此它们存在于属于数据库服务器的每个数据库中。下表列出了所有的固定数据库角色。

固定数据库角色

说明

db_owner

可以执行数据库中技术所有动作的用户

db_accessadmin

可以添加、删除用户的用户

db_datareader

可以查看所有数据库中用户表内数据的用户

db_datawriter

可以添加、修改或删除所有数据库中用户表内数据的用户

db_ddladmin

可以在数据库中执行所有DDL操作的用户

db_securityadmin

可以管理数据库中与安全权限有关所有动作的用户

db_backoperator

可以备份数据库的用户(并可以发布DBCC和CHECKPOINT语句，这两个语句一般在备份前都会被执行)

db_denydatareader

不能看到数据库中任何数据的用户

db_denydatawriter

不能改变数据库中任何数据的用户

除了上表中列出的固定数据库角色之外，还有一种特殊的固定数据库角色，名为public，这里将首先介绍这一角色。

public角色

public角色是一种特殊的固定数据库角色，数据库的每个合法用户都属于该角色。它为数据库中的用户提供了所有默认权限。这样就提供了一种机制，即给予那些没有适当权限的所有用户以一定的(通常是有限的)权限。public角色为数据库中的所有用户都保留了默认的权限，因此是不能被删除的。

一般情况下，public角色允许用户进行如下的操作：

使用某些系统过程查看并显示master数据库中的信息

执行一些不需要一些权限的语句(例如PRINT)

固定数据库角色及其权限

在数据库中，每个固定数据库角色都有其特定的权限。这就意味着对于某个数据库来说，固定数据库角色的成员的权限是有限的。使用系统过程sp_dbfixedrolepermission就可以查看每个固定数据库角色的权限。该系统过程的语法为：

sp_db.xedrolepermission [[@rolename =] 'role']

如果没有指定role的值，那么所有固定数据库角色的权限都可以显示出来。下面的几节将讨论每个固定数据库角色的权限。

1. db_owner

固定数据库角色db_owner的成员可以在特定的数据库中进行如下的动作：

向其他固定数据库角色中添加成员，或从其中删除成员

运行所有的DDL语句

运行BACKUP DATABASE和BACKUP LOG语句

使用CHECKPOINT语句显式地启动检查点进程

运行下列dbcc命令：dbcc checkalloc、dbcc checkcatalog、dbcc checkdb、dbcc updateusage

授予、取消或剥夺每一个数据库对象上的下列权限：SELECT、INSERT、UPDATE、DELETE和REFERENCES

使用下列系统过程向数据库中添加用户或角色：sp_addapprole、sp_addrole、sp_addrolemember、sp_approlepassword、sp_changeobjectowner、sp_dropapprole、sp_droprole、sp_droprolemember、sp_dropuser、sp_grantdbaccess

使用系统过程sp_rename为任何数据库对象重新命名

2. db_accessadmin

固定数据库角色db_accessadmin的成员可以执行与数据库访问有关的所有动作。这些角色可以在具体的数据库中执行下列操作：

运行下列系统过程：sp_addalias、sp_dropalias、sp_dropuser、sp_grantdbacess、sp_revokedbaccess

为Windows用户账户、Windows组和SQL Server登录添加或删除访问

3. dbdatareader

固定数据库角色dbdatareader的成员对数据库中的数据库对象(表或视图)具有SELECT权限。然而，这些成员不能把这个权限授予其他任何用户或角色。(这个限制对REVOKE语句来说同样成立。)

4. dbdatawriter

固定数据库角色dbdatawriter的成员对数据库中的数据库对象(表或视图)具有INSERT、UPDATE和DELETE权限。然而，这些成员不能把这个权限授予其他任何用户或角色。(这个限制对REVOKE语句来说也同样成立。)

5. db_ddladmin

固定数据库角色db_ddladmin的成员可以进行如下的动作：

运行所有DDL语句