Tip: Manage Access and Roles with Transact-SQL (T-SQL) Commands

SQL Server provides different commands for managing database access and roles. Here is a summary of the commands you can use.

Adding a User to the Current Database:

CREATE USER user_name
    [ { { FOR | FROM }
        { LOGIN login_name
        | CERTIFICATE certificate_name
        | ASYMMETRIC KEY asym_key_name
        }
    ]
    [ WITH DEFAULT_SCHEMA = schema_name ]

Renaming a User or Changing Default Schema:

ALTER USER user_name
    WITH < set_item > [ ,...n ]
    
< set_item > ::=
    NAME = new_user_name
    | DEFAULT_SCHEMA = schema_name

Removing a User from a Database:

DROP USER user_name

Listing Server Role Members:

sp_helpsrvrolemember [[@rolename =] 'role']

Managing Database Standard Roles:

CREATE ROLE role_name [ AUTHORIZATION owner_name ]
ALTER ROLE role_name WITH NAME = new_name
DROP ROLE role_name
sp_helprole [[@rolename =] 'role']

Managing Database Role Members:

sp_addrolemember [@rolename =] 'role',
    [@membername =] 'security_account'
sp_droprolemember [@rolename =] 'role',
    [@membername =] 'security_account'
sp_helprolemember [[@rolename =] 'role']

Managing Application Roles:

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

From the Microsoft Press book Microsoft SQL Server 2008 Administrator's Pocket Consultant.

Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.

For more Tips on other products, visit the TechNet Magazine Tips index.