使用T-SQL命令管理存取和角色權限 Manage Access and Roles with Transact-SQL (T-SQL) Commands

SQL Server提供不同的命令來管理資料庫存取和角色.這裡概略的列出你可使用的命令.

增加使用者到目前資料庫

CREATE USER user_name

[ { { FOR | FROM }

{ LOGIN login_name

| CERTIFICATE certificate_name

| ASYMMETRIC KEY asym_key_name

}

]

[ WITH DEFAULT_SCHEMA = schema_name ]

更改使用者名稱或預設的結構描述

ALTER USER user_name

WITH < set_item > [ ,...n ]

< set_item > ::=

NAME = new_user_name

| DEFAULT_SCHEMA = schema_name

從資料庫移除使用者

 DROP USER user_name

傳回伺服器角色成員

 sp_helpsrvrolemember [[@rolename =] 'role']

管理資料庫標準角色

 CREATE ROLE role_name [ AUTHORIZATION owner_name ]

 ALTER ROLE role_name WITH NAME = new_name

 DROP ROLE role_name

 sp_helprole [[@rolename =] 'role']

管理資料庫角色成員

 sp_addrolemember [@rolename =] 'role',

         [@membername =] 'security_account'

 sp_droprolemember [@rolename =] 'role',

         [@membername =] 'security_account'

 sp_helprolemember [[@rolename =] 'role']

管理應用程式角色

 CREATE APPLICATION ROLE application_role_name

         WITH PASSWORD = 'password' [ , DEFAULT_SCHEMA = schema_name ]

 ALTER APPLICATION ROLE application_role_name

         WITH <set_item> [ ,...n ]

 <set_item> ::=

         NAME = new_application_role_name

         | PASSWORD = 'password'

         | DEFAULT_SCHEMA = schema_name

 DROP APPLICATION ROLE rolename

來源出處: Microsoft Press book Microsoft SQL Server 2008 Administrator’s Pocket Consultant