SqlServer服务器角色和数据库角色相关操作 - 数据库编程

- 删除角色 DROP ROLE NewRole -- 当前数据库角色与用户关系 SELECT u.name as UserName,u.type_desc,u.default_schema_name,g.name as DBRole FROM sys.database_principals u inner join sys.database_role_members m on u.principal_id = m.member_principal_id inner join sys.database_principals g on g.principal_id = m.role_principal_id ORDER BY UserName,DBRole --对于数据库级别权限控制更改：某数据库――>安全性――角色――>数据库角色――>右键角色――>可自行添加成员

/*------------------------------------------------------------------------------------
								【数据库级别-应用程序角色】
------------------------------------------------------------------------------------*/

应用程序角色是一个数据库主体，只能通过其他数据库中为 guest 授予的权限来访问这些数据库
其他数据库中的应用程序角色将无法访问任何已禁用 guest 的数据库
可以只允许通过特定应用程序连接的用户访问特定数据
与数据库角色不同的是，应用程序角色默认情况下不包含任何成员，而且是非活动的
启用应用程序角色需要密码
应用程序角色无法访问服务器级元数据，若允许访问，需开启跟踪标志4616（ DBCC TRACEON(4616,-1)）


--	创建应用程序角色
CREATE APPLICATION ROLE [MyAppRole] WITH DEFAULT_SCHEMA = [dbo], PASSWORD = N'AppRole'


--	更改应用程序角色的名称、密码或默认架构
ALTER APPLICATION ROLE [MyAppRole] 
	WITH NAME = [AppRole],
	PASSWORD = 'AppRole',
	DEFAULT_SCHEMA = [dbo]


--	激活与当前数据库中的应用程序角色关联的权限(不能在其他存储过程或用户定义事务中执行)
DECLARE @cookie varbinary(8000)
EXEC sp_setapprole 
  @rolename = 'AppRole'
, @password = 'AppRole'
--, @password = { encrypt N'AppRole' }	--ODBC encrypt 函数加密
, @encrypt = 'none'						--'none'|'odbc'：是否模糊加密处理
, @fCreateCookie = true					-- true|false :是否创建 cookie
, @cookie = @cookie OUTPUT				--获取应用程序角色之前安全上下文
SELECT @cookie	--记住cookie


--	查看当前登录用户（发现为 [AppRole]）
SELECT CURRENT_USER,USER_NAME()


--	停用应用程序角色并恢复到前一个安全上下文(cookie 为sp_setapprole创建的)
EXEC sp_unsetapprole @cookie = 0x5E76DB4EF3E20388C5CBDE4FEB63DC409BAAD344D11919EF23AF18743A8B40BBEB3F972E3D84C5F7FBE2C1A853934EBC0100


--	删除应用程序角色
DROP APPLICATION ROLE [AppRole]


--	查看用程序角色
SELECT * FROM sys.sysusers WHERE isapprole = 1

SqlServer服务器角色和数据库角色相关操作(二)