一：安全对象是SQ L Server控制访问权限的资源，在SQL Server中的安全对象分为3个嵌套范围。


1、层次最高的是服务器范围。包含了登录名，数据库，端点。 

--1.管理服务器权限 use master go   if not exists(select name                from sys.server_principals               where name = 'ggg') begin 	create login [ggg]  	with password = 'ggg', 	     default_database = [master], 	     check_expiration = off, 	     check_policy = off  end   --授予修改跟踪权限 grant alter trace to ggg with grant option  --被授权者有将权限授予其他被授予者的权限   --创建windows登录名 create login [pc0627jvc\wcc] from windows with default_database = [master]   --授予创建数据库,查看数据库权限 grant create any database,       view any database to [pc0627jvc\wcc]  --拒绝关闭服务器权限 deny shutdown to [pc0627jvc\wcc]    --2.查询服务器权限 select p.name,  --授权者        s.name,  --被授权者                sp.class_desc,        sp.permission_name,        sp.state_desc     from sys.server_permissions  sp inner join sys.server_principals p         on p.principal_id =  sp.grantor_principal_id         inner join sys.server_principals s         on s.principal_id = sp.grantee_principal_id where s.name in ('pc0627jvc\wcc','ggg')


2、其次是数据库范围，是包含在服务器范围内的，控制数据库用户，数据库角色，安全凭证，架构等安全对象。 

--1.管理数据库权限 if not exists(select name                from sys.server_principals               where name = 'xyz') begin 	create login [xyz]  	with password ='xyz', 	     default_database =[master], 	     check_expiration = off, 	     check_policy =off 	 end   --创建数据库 if not exists(select name               from sys.databases               where name = 'test') begin 	create database test end   use test go  --创建数据库用户 if not exists(select name               from sys.database_principals               where name = 'xyz') begin 	create user xyz for login xyz end   --给数据库用户授予修改任何程序集、修改任何证书的权限 grant alter any assembly,       alter any certificate to xyz  --拒绝修改数据库级别的ddl触发器的权限 deny alter any database ddl trigger to xyz  revoke connect from xyz   --新建登录,新建数据库用户,并授予权限 use AdventureWorks go  create login testuser  with password = 'testuser',      check_expiration = off,      check_policy = off       create user testuser from login testuser   grant select on humanresources.department to testuser  grant select on production.productphoto to testuser  grant exec on humanresources.uspUpdateEmployeeHireInfo to testuser  grant create assembly to testuser  grant select on schema::person to testuser  deny impersonate on user::dbo to testuser  deny select on humanresources.employee(birthdate) to testuser  grant select on schema::sales to testuser   --2.查询数据库权限 select dppp.name as grantor,       --授权者 	   dppp.type_desc as grantor_type,  --数据库用户对应的登录名类型         	   dpp.name as grantee,  --被授予权限者 	   dpp.type_desc as grantee_type,         	   dp.class_desc, 	   ISNULL(o.type_desc,'') object_type_desc,  --对象类型         	   case when dp.class_desc = 'schema' 				 then SCHEMA_NAME(dp.major_id)                   			when dp.class_desc = 'object_or_column' 				 then case when minor_id = 0 								then OBJECT_NAME(dp.major_id)                                  						   else (select OBJECT_NAME(object_id) + '.' + name 								 from sys.columns  								 where object_id = dp.major_id 									   and column_id = minor_id) 					  end 			else '' 	   end as object_name,   --对象名称         	   dp.permission_name,   --权限名称 	   dp.state_desc         --授予状态		              from sys.database_permissions dp inner join sys.database_principals dpp   --被授予者 		on dp.grantee_principal_id = dpp.principal_id inner join sys.database_principals dppp  --授予者 		on dp.grantor_principal_id = dppp.principal_id left join sys.objects o 	   on o.object_id = dp.major_id where dpp.name = 'testuser'


3、最内层是架构范围，它控制安全对象(架构本身)以及架构中的对象(比如：表、视图、存储过程、函数)。

use test go  --1.架构的拥有者 --1.1创建架构,拥有者是db_owner角色 create schema wcc authorization db_owner   --1.2显示架构的拥有者:db_owner select s.name,       --架构名称        dp.name,      --架构拥有者                dp.type_desc, --拥有者类型        dp.is_fixed_role --是否固定数据库角色        from sys.schemas s inner join sys.database_principals dp         on s.principal_id = dp.principal_id where s.name = 'wcc'   --1.3改变架构的所有权 alter authorization on schema::wcc to wclogin   --1.4显示架构的拥有者:wclogin select s.name,       --架构名称        dp.name,      --架构拥有者                dp.type_desc, --拥有者类型        dp.is_fixed_role --是否固定数据库角色        from sys.schemas s inner join sys.database_principals dp         on s.principal_id = dp.principal_id where s.name = 'wcc'   --1.5在新建架构下,创建表 create table wcc.it 	(it char(13) not null primary key, 	 itcreate datetime not null default getdate() 	)   use master go  --2.用户的默认架构 --2.1创建登录名 create login wclogin with password = 'wclogin',      default_database = test,      check_policy = off,      check_expiration = off       go  use test go   --2.2创建数据库用户,默认架构是dbo create user wclogin for login wclogin go   --2.3用户wclogin的默认架构是wcc alter user wclogin with default_schema = wcc go   --2.4显示用户wclogin的默认架构 select name,                --数据库用户名        type_desc,        default_schema_name  --默认架构 from sys.database_principals where name = 'wclogin'    --3.对象所属的架构 --3.1表it所属的架构 select t.name, --对象名称               s.name  --对象所属架构名称        from sys.tables t inner join sys.schemas s         on t.schema_id = s.schema_id where t.name = 'it'   --3.2把wcc下的表it转移到架构dbo下 alter schema dbo transfer wcc.it   --3.3再次查看表it的架构 select t.name, --对象名称                s.name  --对象所属架构名称        from sys.tables t inner join sys.schemas s         on t.schema_id = s.schema_id where t.name = 'it'   --4.1删除架构wcc,必须先删除架构下所有的对象,或者把架构下所有的对象都转移到其他架构 drop schema wcc   --4.2修改数据库用户wclogin的默认架构 alter user wclogin with default_schema = dbo    use AdventureWorks go  --5.1新建数据库用户名wclogin create user wclogin for login wclogin   --3.2授予wclogin用户，架构person的所有权 grant take ownership on schema::Person to wclogin   --3.3虽然授予了take ownership权限,但是这个架构的拥有者没有变 select s.name,       --架构名称        dp.name,      --架构拥有者                dp.type_desc, --拥有者类型        dp.is_fixed_role --是否固定数据库角色        from sys.schemas s inner join sys.database_principals dp         on s.principal_id = dp.principal_id where s.name = 'person'    --6.管理对象的权限 grant alter,execute,select on schema::Production to wclogin with grant option   grant insert,update,delete on schema::production to wclogin   revoke alter,select on schema::production to wclogin cascade   grant delete,insert,select,update on humanresources.department to wclogin   deny alter on humanresources.department to wclogin   revoke insert,update,delete on humanresources.department  to wclogin   --创建角色 create role reportview  --给角色授予权限 grant execute,view definition on dbo.uspgetmanageremployees to reportview

当前连接在安全对象上的权限

use test go  --1.检测当前连接的是否有安全对象的权限  select HAS_PERMS_BY_NAME(null,                   --安全对象名称                          null,                   --测试权限的安全对象的类名                          'VIEW SERVER STATE')   --要检查的权限名称   select HAS_PERMS_BY_NAME(DB_NAME(),   --安全对象名称                          'database',  --测试权限的安全对象的类名                          'alter')     --要检查的权限名称    /*=================================== 语法：  { EXEC | EXECUTE ] AS <context_specification> [;]  <context_specification>::= { LOGIN | USER } = 'name'     [ WITH { NO REVERT | COOKIE INTO @varbinary_variable } ]  | CALLER  =====================================*/ --模拟数据库用户wclogin,测试是否对当前数据库有任何的权限 EXECUTE AS user = 'wclogin' GO SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY'); GO REVERT;   --是否对表有select的权限 select HAS_PERMS_BY_NAME(SCHEMA_NAME(t.schema_id) + '.' + t.name,                          'OBJECT',                          'select'),        t.* from sys.tables t   --是否对某个表的列有select的权限 select HAS_PERMS_BY_NAME('production.product',                          'object',                          'select',                          c.name,                          'column'),       c.* from sys.columns c where object_id = object_id('production.product')    --2.当前连接,对于某个安全对象,在这个安全对象类上,有多少权限 select * from sys.fn_my_permissions(null,        --安全对象的名称                            'server')    --要列出权限的安全对象类   select * from sys.fn_my_permissions('production',--安全对象的名称                            'schema')    --要列出权限的安全对象类    --3.改变安全对象的所有权 alter authorization on schema::production to wclogin  alter authorization on endpoint::端点名称 to wclogin    --4.sql登录名绑定到凭据，可以访问非SQL Server的资源 create credential account with identity = 'pc0627jvc\wcc',      secret = 'wcc'       --把一个凭据绑定到一个登录名上      alter login wclogin with credential = account   --查询凭据 select * from  sys.credentials where name = 'account'   --查询登录名是否绑定到凭据 select sp.name, --登录名        c.name   --凭据名 from sys.server_principals sp inner join sys.credentials c         on sp.credential_id = c.credential_id where sp.name = 'wclogin'



二：权限允许主体在安全对象上执行操作。grant、deny、revoke命令可以用于所有的安全对象范围，用来控制主体到安全对象的访问。grant用于启用对安全对象的访问；deny用于限制访问，可以禁止主体对安全对象的访问权限；revoke可以回收主体对安全对象的访问权限。

 

sys.fn_builtin_permissions ( [ DEFAULT | NULL ]     | empty_string | '<securable_class>' } )  <securable_class> ::=        APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY     | CERTIFICATE | CONTRACT | DATABASE | ENDPOINT | FULLTEXT CATALOG     | LOGIN | MESSAGE TYPE | OBJECT | REMOTE SERVICE BINDING | ROLE     | ROUTE | SCHEMA | SERVER | SERVICE | SYMMETRIC KEY | TYPE      | USER | XML SCHEMA COLLECTION

  

--显示整个服务器所有的权限的层次结构、覆盖结构 ;with temp   --显示每个有覆盖权限的权限，在同一权限类别中的覆盖层次，用\\来划分 as ( select class_desc,        permission_name,                covering_permission_name,                parent_class_desc,        parent_covering_permission_name,                cast('( '+class_desc + ' ){' + permission_name +'} \\  ' as varchar(6000)) as c,        1 as level         from sys.fn_builtin_permissions(default) r where --parent_class_desc = ''        covering_permission_name <> ''  union all  select t.class_desc,        t.permission_name,                r.covering_permission_name,                t.parent_class_desc,        t.parent_covering_permission_name,                       CAST('( ' + r.class_desc + ' ){ ' + r.permission_name + ' } \\ ' + c as varchar(6000)),        level + 1  from temp t inner join sys.fn_builtin_permissions(default) r         on t.class_desc = r.class_desc            and t.covering_permission_name = r.permission_name --where r.covering_permission_name <> ''  ),   t    --显示每个有覆盖权限的权限，在同一权限类别中的覆盖层次，求level最大的 as (  	select * 	from temp t 	where level =  	( 		select level 		from 		(     			 select class_desc, 			        permission_name, 					MAX(level) as level 			 from temp  			 group by class_desc, 			          permission_name 		)a 		where a.class_desc = t.class_desc 		      and a.permission_name = t.permission_name 	) ),   tt   --对已经显示的覆盖层次，进一步求出上层权限类别的级联层次，用--->>>来划分 as ( select class_desc,        permission_name,                parent_class_desc,        parent_covering_permission_name,                    cast(c as varchar(6000)) as c,                1 as level from t  union all  select tt.class_desc,        tt.permission_name,                r.parent_class_desc,        r.parent_covering_permission_name,                cast(r.c + ' --->>> '  + tt.c as varchar(6000)) as c ,        tt.level + 1 as level       from tt inner join ( 	            SELECT  class_desc, 	                    permission_name, 	        	                    parent_class_desc, 	                    parent_covering_permission_name, 	            	                    cast(c as varchar(6000)) as c 	            FROM t 	             	            UNION ALL 	             	            select class_desc, 	                   permission_name,      			     			         	        	       			   parent_class_desc,             	       			   parent_covering_permission_name, 	       			   CAST('( ' + class_desc + ' ){ ' + permission_name + ' }'AS VARCHAR(6000)) AS c  	 	            from sys.fn_builtin_permissions(default) 	            WHERE covering_permission_name ='' 	       ) r 	                on r.class_desc = tt.parent_class_desc            and r.permission_name = tt.parent_covering_permission_name              ),   ttt   --对于没有覆盖权限的权限，直接求上层权限类别的级联层次，用--->>>来划分 as ( select class_desc,        permission_name,                parent_class_desc,        parent_covering_permission_name,                    cast('( ' + class_desc + ' ){ ' + permission_name + ' } --->>> '  as varchar(6000)) as c,                1 as level from sys.fn_builtin_permissions(default)  where covering_permission_name = ''  union all  select ttt.class_desc,        ttt.permission_name,                r.parent_class_desc,        r.parent_covering_permission_name,                cast('( ' + r.class_desc + ' ){ ' + r.permission_name + ' } --->>> '  + ttt.c as varchar(6000)) as c ,        ttt.level + 1 as level       from ttt inner join sys.fn_builtin_permissions(default) r         on r.class_desc = ttt.parent_class_desc            and r.permission_name = ttt.parent_covering_permission_name       )  	select class_desc, 	       permission_name, 	       c                  --有覆盖权限的权限 	from  tt 	where level =  	( 		select level 		from 		(     			 select class_desc, 			        permission_name, 					MAX(level) as level 			 from tt  			 group by class_desc, 			          permission_name 		)a 		where a.class_desc = tt.class_desc 		      and a.permission_name = tt.permission_name 	) 	 	union all   	 	select class_desc, 	       permission_name, 	       c                --没有覆盖权限的权限 	from  ttt 	where level =  	( 		select level 		from 		(     			 select class_desc, 			        permission_name, 					MAX(level) as level 			 from ttt  			 group by class_desc, 			          permission_name 		)a 		where a.class_desc = ttt.class_desc 		      and a.permission_name = ttt.permission_name 	)	   order by 1