TechNet Magazine > Home > Tips > SQL Server >  Manage Access and Roles with Transact-SQL (T-SQ...
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



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.
Page view tracker