ALTER USER (Transact-SQL)
Renames a database user or changes its default schema.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
ALTER USER user_name
WITH <set_item> [ ,...n ]
<set_item> ::=
NAME = new_user_name
| DEFAULT_SCHEMA = schema_name
| LOGIN = login_name
Arguments
- user_name
Specifies the name by which the user is identified inside this database.
- LOGIN **=**login_name
Re-maps a user to another login by changing the user's Security Identifier (SID) to match the login's SID.
- NAME **=**new_user_name
Specifies the new name for this user. new_user_name must not already exist in the current database.
- DEFAULT_SCHEMA **=**schema_name
Specifies the first schema that will be searched by the server when it resolves the names of objects for this user.
Remarks
If DEFAULT_SCHEMA is left undefined, the user will have dbo as its default schema. DEFAULT_SCHEMA can be set to a schema that does not currently exist in the database. Therefore, you can assign a DEFAULT_SCHEMA to a user before that schema is created. DEFAULT_SCHEMA cannot be specified for a user that is mapped to a Windows group, a certificate, or an asymmetric key.
Important
The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.
You can only change the name of a user that is mapped to a Windows login or group when the SID of the new user name matches the SID that is recorded in the database. This check helps prevent spoofing of Windows logins in the database.
The WITH LOGIN clause allows the remapping of a user to a different login. Users without a login, users mapped to a certificate, or users mapped to an asymmetric key cannot be re-mapped with this clause. Only SQL Server users and Windows users (or groups) can be remapped. The WITH LOGIN clause cannot be used to change the type of user, such as changing a Windows account to a login.
The name of the user will be automatically renamed to the login name if the user is a Windows user, if its name is a Windows name (contains a backslash), or if no new name was specified for it and its current name is different from the login name. Otherwise, the user will not be renamed unless the caller additionally invokes the NAME clause.
Note
A user with ALTER ANY USER permission can change the default schema of any user. A user with an altered schema might unknowingly select data from the wrong table or execute code from the wrong schema.
The name of a user mapped to a SQL Server login, a certificate, or an asymmetric key cannot contain the backslash character (\).
Warning
In SQL Server 2005 the behavior of schemas is changed from the behavior in earlier versions of SQL Server. Code that assumes that schemas are equivalent to database users may not return correct results. Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements has ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. In a database in which any of these statements has ever been used, you must use the new catalog views. The new catalog views take into account the separation of principals and schemas that is introduced in SQL Server 2005. For more information about catalog views, see Catalog Views (Transact-SQL).
Permissions
To change the name of a user requires ALTER ANY USER on the database. To change the default schema, requires ALTER permission on the user. A user can change its own default schema.
Requires CONTROL permission on the database to remap a user to a login.
Examples
A. Changing the name of a database user
The following example changes the name of the database user Mary5 to Mary51.
USE AdventureWorks;
ALTER USER Mary5 WITH NAME = Mary51;
GO
B. Changing the default schema of a user
The following example changes the default schema of the user Mary51 to Purchasing.
USE AdventureWorks;
ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;
GO
See Also
Reference
CREATE USER (Transact-SQL)
DROP USER (Transact-SQL)
EVENTDATA (Transact-SQL)