SQL Server ALTER USER

Summary: in this tutorial, you’ll learn how to use SQL Server ALTER USER statement to change a user in the current database.

Introduction to the SQL Server ALTER USER statement #

The ALTER USER statement allows you to modify the properties of an existing user. The ALTER USER statement allows you to:

To rename a user, you use the ALTER USER ... WITH NAME statement:

ALTER USER user_name
WITH NAME new_name;Code language: SQL (Structured Query Language) (sql)

First, create a new login called zack:

CREATE LOGIN zack 
WITH PASSWORD = 'Zu$c3suik.';Code language: SQL (Structured Query Language) (sql)

Second, create a user for the same login:

CREATE USER zack
FOR LOGIN zack;Code language: SQL (Structured Query Language) (sql)

Third, change the name of the user zack to zachary:

ALTER USER zack
WITH NAME = zachary;Code language: SQL (Structured Query Language) (sql)

To change the default schema of a user to another, you use the ALTER USER .. WITH DEFAULT_SCHEMA statement:

ALTER USER user_name
WITH DEFAULT_SCHEMA = new_schema;Code language: SQL (Structured Query Language) (sql)

For example, the following statement changes the default schema of the user zachary to sales:

ALTER USER zachary
WITH DEFAULT_SCHEMA = sales;Code language: SQL (Structured Query Language) (sql)

To map the user with another login account, you use following ALTER USER ... WITH LOGIN statement:

ALTER USER user_name
WITH LOGIN = new_login;Code language: SQL (Structured Query Language) (sql)

For example, the following statements create a new login and map it with the user zachary:

CREATE LOGIN zachary
WITH PASSWORD = 'Na%c8suik#';

ALTER USER zachary
WITH LOGIN = zachary;Code language: SQL (Structured Query Language) (sql)

The following statement changes the name, default schema, and login of the user zachary:

ALTER USER zachary
WITH NAME = zack,
     LOGIN = zack,
     DEFAULT_SCHEMA = production;Code language: SQL (Structured Query Language) (sql)

Use the ALTER USER statement to change the name of a user, map it with a new login and change the default schema.

Was this tutorial helpful?