SqlServer 伺服器角色和資料庫角色相關操作，sqlserver資料庫

SqlServer 伺服器角色和資料庫角色相關操作，sqlserver資料庫

/*------------------------------------------------------------------------------------【伺服器層級—伺服器角色】------------------------------------------------------------------------------------*/--查看固定伺服器角色(8個,不可增刪，未包括public,每個使用者都屬於public伺服器角色)EXEC sp_helpsrvroleEXEC sp_helpsrvrole @srvrolename = 'sysadmin'--查看伺服器角色所擁有的許可權說明（伺服器角色詳細許可權說明！）EXEC sp_srvrolepermissionEXEC sp_srvrolepermission @srvrolename = 'sysadmin'--判斷某個登入帳號是否擁有某個伺服器角色(有則為1,否則為0)SELECT IS_SRVROLEMEMBER('sysadmin')SELECT IS_SRVROLEMEMBER('sysadmin','sa')--伺服器角色在 SQL Server 2012 中可以建立!~CREATE SERVER ROLE [ServerRoleKK]--將登入名稱添加為某個伺服器級角色的成員EXEC sp_addsrvrolemember @loginame= 'kk' ,@rolename = 'sysadmin' --從伺服器級角色中刪除 SQL Server 登入名或 Windows 使用者或組EXEC sp_dropsrvrolemember @loginame = 'kk' ,@rolename = 'sysadmin'--查看 伺服器角色 和 成員 關係EXEC sp_helpsrvrolememberEXEC sp_helpsrvrolemember @srvrolename = 'sysadmin'--查看 伺服器角色 和 成員 關係(更詳細)SELECT rsp.principal_id as [role_principal_id],rsp.name AS [Server_Role],sp.principal_id,sp.name,sp.[sid],CASE WHEN sp.[type]='S' THEN 'SQL 登入名稱'WHEN sp.[type]='U' THEN 'Windows 登入名稱'WHEN sp.[type]='G' THEN 'Windows 組'WHEN sp.[type]='R' THEN '伺服器角色'WHEN sp.[type]='C' THEN '映射到認證的登入名稱'WHEN sp.[type]='K' THEN '映射到非對稱金鑰的登入名稱'END AS [Principal_Type],sp.create_date,sp.modify_date,sp.is_disabledFROM sys.server_principals sp INNER JOIN sys.server_role_members srm ON sp.principal_id=srm.member_principal_id INNER JOIN sys.server_principals rsp ON srm.role_principal_id=rsp.principal_id--查看伺服器對象許可權控制情況SELECT sp1.principal_id AS grantor_principal_id,sp1.name AS grantor_name,sp1.type_desc AS grantor_type_desc,sp2.principal_id AS grantee_principal_id,sp2.name AS grantee_name,sp2.type_desc AS grantee_type_desc,spe.class_desc,spe.state_desc,spe.permission_nameFROM sys.server_principals sp1INNER JOIN sys.server_permissions spe ON sp1.principal_id=spe.grantor_principal_idINNER JOIN sys.server_principals sp2 ON sp2.principal_id=spe.grantee_principal_id--對於伺服器層級許可權控制更改：安全性——>登入名稱——右鍵登入帳號——>屬性——>安全性實體——>搜尋（有伺服器/端點/登入名稱）——>下方控制許可權

/*------------------------------------------------------------------------------------【資料庫層級—資料庫角色】------------------------------------------------------------------------------------*/--查看固定資料庫角色（不包括public角色，每個資料庫使用者都屬於 public 資料庫角色）EXEC sp_helpdbfixedroleEXEC sp_helpdbfixedrole @rolename = 'db_owner'--查看資料庫角色所擁有的許可權說明（資料庫角色詳細許可權說明！）EXEC sp_dbfixedrolepermissionEXEC sp_dbfixedrolepermission @rolename = 'db_owner'--當前資料庫中有關角色的資訊（至少10個固定資料庫角色）EXEC sp_helproleEXEC sp_helprole @rolename = 'db_owner'--IsAppRole：是否應用程式角色--某個角色的成員的資訊EXEC sp_helprolememberEXEC sp_helprolemember @rolename = 'db_owner'--目前使用者是否為指定 Microsoft Windows 組或 SQL Server 資料庫角色的成員SELECT IS_MEMBER('db_owner')--建立資料庫角色（如建立角色 Myrole 並擁有db_owner角色）CREATE ROLE MyRole AUTHORIZATION db_owner--授予角色許可權（當前資料庫中將資料庫角色授予 資料庫使用者、資料庫角色、Windows登入名稱或Windows 組）EXEC sp_addrolemember @rolename= 'MyRole',@membername = 'MyUser'--回收角色許可權（當前資料庫中將資料庫角色回收，同上相反)EXEC sp_addrolemember @rolename= 'MyRole',@membername = 'MyUser'--更改角色名稱ALTER ROLE MyRole WITH NAME = NewRole--刪除角色DROP ROLE NewRole--當前 資料庫角色 與 使用者 關係SELECT u.name as UserName,u.type_desc,u.default_schema_name,g.name as DBRoleFROM sys.database_principals uinner join sys.database_role_members m on u.principal_id = m.member_principal_idinner join sys.database_principals g on g.principal_id = m.role_principal_idORDER 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