Skip to main content
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


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.