SQL语句操作SQLSERVER数据库登录名、⽤户及权限
要想成功访问 SQL Server 数据库中的数据，我们需要两个⽅⾯的授权：
1. 获得准许连接 SQL Server 服务器的权利；
2. 获得访问特定数据库中数据的权利（select, update, delete, create table ...）。
假设，我们准备建⽴⼀个 dba 数据库帐户，⽤来管理数据库 mydb。
1. ⾸先在 SQL Server 服务器级别，创建登陆帐户（create login）
--创建登陆帐户（create login）
create login dba with password='abcd1234@', default_database=mydb
登陆帐户名为：“dba”，登陆密码：abcd1234@”，默认连接到的数据库：“mydb”。这时候，dba 帐户就可以连接到 SQL Server 服务器上了。但是此时还不能访问数据库中的对象（严格的说，此时 dba 帐户默认是 guest 数据库⽤户⾝份，可以访问 guest 能够访问的数据库对象）。
要使 dba 帐户能够在 mydb 数据库中访问⾃⼰需要的对象，需要在数据库 mydb 中建⽴⼀个“数据库⽤户
”，赋予这个“数据库⽤户” 某些访问权限，并且把登陆帐户“dba” 和这个“数据库⽤户” 映射起来。习惯上，“数据库⽤户” 的名字和 “登陆帐户”的名字相同，即：“dba”。创建“数据库⽤户”和建⽴映射关系只需要⼀步即可完成：
2. 创建数据库⽤户（create user）：
--为登陆账户创建数据库⽤户（create user），在mydb数据库中的security中的user下可以到新创建的dba
create user dba for login dba with default_schema=dbo
并指定数据库⽤户“dba” 的默认 schema 是“dbo”。这意味着⽤户“dba” 在执⾏“select * from t”，实际上执⾏的是 “select * from dbo.t”。
3. 通过加⼊数据库⾓⾊，赋予数据库⽤户“dba”权限：
--通过加⼊数据库⾓⾊，赋予数据库⽤户“db_owner”权限
exec sp_addrolemember 'db_owner', 'dba'
此时，dba 就可以全权管理数据库 mydb 中的对象了。
如果想让 SQL Server 登陆帐户“dba”访问多个数据库，⽐如 mydb2。可以让 sa 执⾏下⾯的语句：
--让 SQL Server 登陆帐户“dba”访问多个数据库
use mydb2
go create user dba for login dba with default_schema=dbo
go exec sp_addrolemember 'db_owner', 'dba' go
此时，dba 就可以有两个数据库 mydb, mydb2 的管理权限了！
完整的代码⽰例
--创建数据库mydb和mydb2
--在mydb和mydb2中创建测试表，默认是dbo这个schema
CREATE TABLE DEPT
(DEPTNO int primary key,
DNAME VARCHAR(14),
LOC VARCHAR(13) );
--插⼊数据
INSERT INTO DEPT VALUES (101, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (201, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (301, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (401, 'OPERATIONS', 'BOSTON');
--查看数据库schema, user 的存储过程
select * from sys.database_principals
select * from sys.schemas
select * from sys.server_principals
-
-创建登陆帐户（create login）
create login dba with password='abcd1234@', default_database=mydb
--为登陆账户创建数据库⽤户（create user），在mydb数据库中的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 mydb2
go create user dba for login dba with default_schema=dbo
go exec sp_addrolemember 'db_owner', 'dba'go
--禁⽤登陆帐户
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
-
-删除数据库⽤户：
drop user dba
--删除 SQL Server登陆帐户：
drop login dba
使⽤存储过程来完成⽤户创建
下⾯⼀个实例来说明在sqlserver中如何使⽤存储过程创建⾓⾊，重建登录，以及如何为登录授权等问题。
[sql]
1. /*--⽰例说明
2. ⽰例在数据库InsideTSQL2008中创建⼀个拥有表HR.Employees的所有权限、拥有表Sales.Orders的SELECT权限的⾓⾊r_test
3. 随后创建了⼀个登录l_test，然后在数据库InsideTSQL2008中为登录l_test创建了⽤户账户u_test
4. 同时将⽤户账户u_test添加到⾓⾊r_test中，使其通过权限继承获取了与⾓⾊r_test⼀样的权限
5. 最后使⽤DENY语句拒绝了⽤户账户u_test对表HR.Employees的SELECT权限。
6. 经过这样的处理，使⽤l_test登录SQL Server实例后，它只具有表Sales.Orders的select权限和对表HR.Employees出select外的所有
权限。
7. --*/
8.
9.
10. USE InsideTSQL2008
11.
12. --创建⾓⾊ r_test
13. EXEC sp_addrole 'r_test'
14.
15. --添加登录 l_test，设置密码为pwd，默认数据库为pubs
16. EXEC sp_addlogin 'l_test','a@cd123','InsideTSQL2008'
17.
18. --为登录 l_test 在数据库 pubs 中添加安全账户 u_test
19. EXEC sp_grantdbaccess 'l_test','u_test'
20.
21. --添加 u_test 为⾓⾊ r_test 的成员
22. EXEC sp_addrolemember 'r_test','u_test'
安装sql server数据库没到23.
24.
25. --⽤l_test登陆，发现在SSMS中不到仍和表，因此执⾏下述两条语句出错。
26. select * from Sales.Orders
27. select * from HR.Employees
28.
29. --授予⾓⾊ r_test 对 HR.Employees 表的所有权限
30. GRANT ALL ON HR.Employees TO r_test
31. --The ALL permission is deprecated and maintained only for compatibility.
32. --It DOES NOT imply ALL permissions defined on the entity.
33. --ALL 权限已不再推荐使⽤，并且只保留⽤于兼容性⽬的。它并不表⽰对实体定义了 ALL 权限。
34.
35. --测试可以查询表HR.Employees，但是Sales.Orders⽆法查询
36. select * from HR.Employees
37.
38.
39. --如果要收回权限，可以使⽤如下语句。（可选择执⾏）
40. revoke all on HR.Employees from r_test
41. --ALL 权限已不再推荐使⽤，并且只保留⽤于兼容性⽬的。它并不表⽰对实体定义了 ALL 权限。
42.
43.
44. --授予⾓⾊ r_test 对 Sales.Orders 表的 SELECT 权限
45. GRANT SELECT ON Sales.Orders TO r_test
46.
47. --⽤l_test登陆，发现可以查询Sales.Orders和HR.Employees两张表
48. select * from Sales.Orders
49. select * from HR.Employees
50.
51. --拒绝安全账户 u_test 对 HR.Employees 表的 SELECT 权限
52. DENY SELECT ON HR.Employees TO u_test
53.
54. --再次执⾏查询HR.Employees表的语句，提⽰：拒绝了对对象 'Employees' (数据库 'InsideTSQL2008'，架构 'HR')的 SELECT 权限。
55. select * from HR.Employees
56.
57. --重新授权
58. GRANT SELECT ON HR.Employees TO u_test
59.
60. --再次查询，可以查询出结果。
61. select * from HR.Employees
62.
63.
64. USE InsideTSQL2008
65. --从数据库中删除安全账户,failed
66. EXEC sp_revokedbaccess 'u_test'
67. --删除⾓⾊ r_test,failed
68. EXEC sp_droprole 'r_test'
69. --删除登录 l_test,success
70. EXEC sp_droplogin 'l_test'
revoke 与 deny的区别
revoke：收回之前被授予的权限
deny：拒绝给当前数据库内的安全帐户授予权限并防⽌安全帐户通过其组或⾓⾊成员资格继承权限。⽐如UserA所在的⾓⾊组有inset权限，但是我们Deny UserA使其没有insert权限，那么以后即使UserA再怎么到其他含有Insert的⾓⾊组中去，还是没有insert权限，除⾮该⽤户被显⽰授权。
简单来说，deny就是将来都不许给，revoke就是收回已经给予的。
实例
[sql]
1. GRANT INSERT ON TableA TO RoleA
2. GO
3. EXEC sp_addrolemember RoleA, 'UserA' -- ⽤户UserA将有TableA的INSERT权限
4. GO
5.
6. REVOKE INSERT ON TableA FROM RoleA -- ⽤户UserA将没有TableA的INSERT权限，收回权限
7. GO
8.
9. GRANT INSERT ON TableA TORoleA --重新给RoleA以TableA的INSERT权限
10. GO
11.
12. DENY INSERT ON TableA TO UserA -- 虽然⽤户UserA所在RoleA有TableA的INSERT权限，但UserA本⾝被DENY了，所以⽤户
UserA将没有TableA的INSERT权限。
13. 摘⾃：blog.csdn/a497785609/article/details/47686659
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论