sql server权限相关的sql语句总结

注：本文只是记录自己的操作，不保证正确，请勿参考

--查询sql server的所有权限,很重要，要授予权限，总得先知道有哪些权限可以授予
select * from sys.fn_builtin_permissions(DEFAULT)

--查询schema的所有权限
select * from sys.fn_builtin_permissions('schema')

--查询server的所有权限
select * from sys.fn_builtin_permissions('server')

--查询database的所有权限
select * from sys.fn_builtin_permissions('database')




--------------------------------------------服务器范围内的权限开始--------------------------------------------
--服务器范围内的权限是授予给windows级或者sql server级的主体的

--授予服务器范围内的权限时，必须切换到master数据库
use master

--授予创建任何数据库，查看任何数据库的权限给x1
grant create any database, view any database to x1

--明确登陆名x1拒绝关闭实例的权限
deny shutdown to x1

--撤销授予的权限
revoke create any database, view any database from x1

--查看windows级别主体或者sql server级别主体被授予的权限
select ps.class_desc, ps.permission_name, ps.state_desc, pr.name from sys.server_permissions as ps
inner join sys.server_principals as pr 
on ps.grantee_principal_id = pr.principal_id
where pr.name='x1'
--------------------------------------------服务器范围内的权限结束--------------------------------------------





--------------------------------------------数据库范围内的权限开始--------------------------------------------
--数据库范围内的权限授予数据库级的主体

--查询所有数据库级别的主体
select * from sys.database_principals

--查询所有sql server和windows级别的主体
select * from sys.server_principals


--切换数据库，授予数据库范围内的权限时，要先连接数据库
use mybatiesTest1

--授予数据库级主体d1连接mybatiesTest1数据库的权限
grant connect to d1

--授予数据库级主体d1完全控制mybatiesTest1数据库的权限
grant control to d1

--授予数据库级主体d2对数据库mybatiesTest1中的表或者视图 select 的权限
--授予select权限之后，数据库mybatiesTest1中的表对d2都是可见的
grant select to d2

--取消授予数据库级主体d1连接mybatiesTest1数据库的权限
revoke connect to d1

--取消授予数据库级主体d1完全控制mybatiesTest1数据库的权限
revoke control to d1

--------------------------------------------数据库范围内的权限结束--------------------------------------------






--------------------------------------------架构范围内的权限开始--------------------------------------------
--创建名为 sch1 的schema
--create schema sch1

--创建架构sch2，将sch2的所有者设置成数据库级主体d1
--create schema sch2 authorization d1

--使用名为sch1的schema创建一张表
--create table sch1.stu(id int identity(1, 1) primary key, name varchar(32), age int)

--insert into sch1.stu(name, age) values('zhangsan', 20)

--select * from sch1.stu

--将架构sch1中的stu表移动到架构dbo中去，只有将架构中的对象清空了（移动到其他架构中去），才能删除架构
--alter schema dbo transfer sch1.stu

--删除schema, 如果schema中含有对象（表，视图之类的），则无法删除schema
drop schema sch1

--查询所有的架构信息
select * from sys.schemas

--授予数据库级主体d2 对架构sch2（这个架构属于数据库级主体d1）中的表或者视图执行select的权限
grant select on schema ::sch2 to d2

--取消授予数据库级主体d2对数据库mybatiesTest1中的表 select 的权限
revoke select on schema ::sch2 to d2


--------------------------------------------架构范围内的权限结束--------------------------------------------