使用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

Comments (0)

Skip to main content