【自学笔记】在SQL Server中创建用户角色及授权(使用SQL语句)更新2023.07.06

04-11 1076阅读

--更新2023.07.06
--1. 首先在 SQL Server 服务器级别，创建登陆帐户（create login）
--2. 创建数据库用户（create user）：
--3. 通过加入数据库角色，赋予数据库用户“dba”权限：
--创建登陆帐户（create login）
create login dba with password='abcd1234@', default_database=AliSysDB
--这时候，dba 帐户就可以连接到 SQL Server 服务器上了。但是此时还不能 访问数据库中的对象
--（严格的说，此时 dba 帐户默认是 guest 数据库用户身份， 可以访问 guest 能够访问的数据库对象）。
--要使 dba 帐户能够在 AliSysDB 数据库中访问自己需要的对象， 需要在数据库 AliSysDB 中建立一个“数据库用户”，
--赋予这个“数据库用户” 某些访问权限，并且把登陆帐户“dba” 和这个“数据库用户” 映射起来。
--创建“数据库用户”和建立映射关系只需要一步即可完成
--为登陆账户创建数据库用户（create user）,在AliSysDB数据库中的security中的user下可以找到新创建的dba
create user dba for login dba with default_schema=dbo
--通过加入数据库角色，赋予数据库用户“db_owner”权限
exec sp_addrolemember 'db_owner', 'dba'
----让 SQL Server 登陆帐户“dba”访问多个数据库
use DBErp
create user dba for login dba with default_schema=dbo
exec sp_addrolemember 'db_owner', 'dba'
-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--删除数据库用户：
drop user dba
--删除 SQL Server登陆帐户：
drop login dba
--禁用登陆帐户
alter login dba disable
--启用登陆帐户
alter login dba enable
--登陆帐户改名
alter login dba with name=dba_tom
--登陆帐户改密码：
alter login dba with password='aabb@ccdd'
--数据库用户改名：
alter user dba with name=dba_tom
--更改数据库用户 defult_schema：
alter user dba with default_schema=sales

--使用T-SQL创建用户
--添加角色
use AliSysDB
go
sp_addlogin  'test_db','123456','AliSysDB' 
go
sp_addsrvrolemember   'test_db','sysadmin'   
go
sp_adduser 'test_db','test_db','db_owner'
go

--使用存储过程来完成用户创建
--下面一个实例来说明在sqlserver中如何使用存储过程创建角色，重建登录，以及如何为登录授权等问题
USE DBErp
 
--创建角色 r_test
EXEC sp_addrole 'r_test'
--添加登录 l_test,设置密码为pwd,默认数据库为pubs
EXEC sp_addlogin 'l_test','a@cd123','DBErp'
--为登录 l_test 在数据库 pubs 中添加安全账户 u_test
EXEC sp_grantdbaccess 'l_test','u_test'
--添加 u_test 为角色 r_test 的成员
EXEC sp_addrolemember 'r_test','u_test'
--授予角色 r_test 对 效期查询 表的所有权限
GRANT ALL ON 效期查询 TO r_test
--ALL 权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。
--如果要收回权限，可以使用如下语句。（可选择执行）
revoke all on 效期查询 from r_test
--授予角色 r_test 对 实时库存查询 表的 SELECT 权限
GRANT SELECT ON 实时库存查询 TO r_test
--用l_test登陆,发现可以查询Sales.Orders和实时库存查询两张表
select * from 效期查询
select * from 实时库存查询
--拒绝安全账户 u_test 对 效期查询 表的 SELECT 权限
DENY SELECT ON 效期查询 TO u_test
--重新授权
GRANT SELECT ON 效期查询 TO u_test
--从数据库中删除安全账户,failed
EXEC sp_revokedbaccess 'u_test'
--删除角色 r_test,failed
EXEC sp_droprole 'r_test'
--删除登录 l_test,success
EXEC sp_droplogin 'l_test'
--revoke 与 deny的区别
--revoke：收回之前被授予的权限
--deny：拒绝给当前数据库内的安全帐户授予权限并防止安全帐户通过其组或角色成员资格继承权限。比如UserA所在的角色组有inset权限，但是我们Deny UserA使其没有insert权限，那么以后即使UserA再怎么到其他含有Insert的角色组中去，还是没有insert权限，除非该用户被显示授权。
--简单来说，deny就是将来都不许给，revoke就是收回已经给予的。
GRANT INSERT ON TableA TO RoleA
GO
EXEC sp_addrolemember RoleA, 'UserA' -- 用户UserA将有TableA的INSERT权限
GO
 
REVOKE INSERT ON TableA FROM RoleA -- 用户UserA将没有TableA的INSERT权限，收回权限
GO
 
GRANT INSERT ON TableA TORoleA --重新给RoleA以TableA的INSERT权限
GO 
 
DENY INSERT ON TableA TO UserA -- 虽然用户UserA所在RoleA有TableA的INSERT权限，但UserA本身被DENY了，所以用户UserA将没有TableA的INSERT权限。

使用SSMS数据库管理工具创建用户登录，这个可视化操作比起用sql语句来创建是在是简单多了

一丶创建用户

登录数据库，在安全性→登录名（鼠标右击）→新建登录名

在弹出新建登录名窗口的“常规”中，输入登录名和密码，密码一定要设置复杂一点，要不然会报错，如果想设置简单的密码也是可以的，把“强制实施密码策略”√去掉就行了。

二丶设置管理员权限

点击左边选择页中的“服务器角色”，来给新建用户授予权限,在右侧的服务器角色面板中,勾选public

注意：如果建立的账号不需要具备系统管理员的权限时，则不要勾选sysadmin 项！

服务器角色说明

sysadmin 执行SQL Server中的任何操作

serveradmin 配置服务器设置

setupadmin 安装复制和管理扩展过程

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

processadmin 管理SQL Server进程

dbcreator 创建和修改数据库

diskadmin 管理磁盘文件

三丶设置非管理员权限

点击左边选择页中的“用户映射”，选择用户可以登录的数据库、选择用户拥有的登录数据库的权限。并在该面板下面的【数据库角色成员身份】中勾选db_owner项！

注意：如果给你个无系统管理权限的账号指定管理一个数据库时，则一定要勾选db_owner项！否则该账号无法看到该数据库中的任何数据表！

让新建的用户dba对多个数据库都拥有权限，

接着上步继续做，再点其他数据库，然后在勾选下面相应的权限即可。这样一次性就能对用户赋予多个数据库不同的权限。

'db_owner' --拥有数据库全部权限，包括删除数据库权限

'db_accessadmin' --只给数据库用户创建其他数据库用户的权限，而没有创建登录用户的权限。

'db_securityadmin' --可以管理全部权限、对象所有权、角色和角色成员资格

'db_ddladmin' --可以发出所有DDL(Create,Alter和Drop)，但不能发出GRANT、REVOKE或DENY语句

'db_backupoperator' --允许对数据库进行备份和还原的权限【备份与还原是通过sql sever management studio也可以进行】
'db_datareader' --可以选择数据库内任何用户表中的所有数据
'db_datawriter' --可以更改数据库内任何用户表中的所有数据

'db_denydatareader' --不能查询数据库内任何用户表中的任何数据

'db_denydatawriter' --不能更改数据库内任何用户表中的任何数据