SQL Server Mentalist

Hi Friends

Continuing from last post on ‘A to Z’ SQL Command Series, In this post we are going to cover Alter User command

Pervious links of this series are as follows

• BISQL # 101 : ‘A to Z’ SQL Command with Syntax, Description, Usage and Example # 1 : ALTER DATABASE
• BISQL # 99 – ‘A to Z’ SQL Command # 1 – List of Command which are present in SQL Server 2008 R2 AND SQL Server 2012
• Starting of Brand New Series – ‘A to Z’ SQL Command Series

Our list for ‘A to Z’ SQL Command Series is as follows :-

ALTER DATABASE , ALTER GROUP, ALTER TABLE, ALTER TRIGGER, ALTER USER (This Post), BEGIN, CHECKPOINT, CLOSE, COMMIT, CREATE AGGREGATE, CREATE CAST, CREATE CONSTRAINT TRIGGER, CREATE CONVERSION, CREATE DATABASE, CREATE DOMAIN, CREATE FUNCTION, CREATE GROUP, CREATE INDEX, CREATE LANGUAGE, CREATE OPERATOR, CREATE OPERATOR CLASS, CREATE RULE, CREATE SCHEMA, CREATE SEQUENCE, CREATE TABLE, CREATE TABLE AS, CREATE TRIGGER, CREATE TYPE, CREATE USER, CREATE VIEW, DEALLOCATE, DECLARE, DELETE, DROP AGGREGATE, DROP CAST, DROP CONVERSION, DROP DATABASE, DROP DOMAIN, DROP FUNCTION, DROP GROUP, DROP INDEX, DROP LANGUAGE, DROP OPERATOR, DROP OPERATOR CLASS, DROP RULE, DROP SCHEMA, DROP SEQUENCE, DROP TABLE, DROP TRIGGER, DROP TYPE, DROP USER, DROP VIEW, END, EXECUTE, FETCH, GRANT, INSERT, LOAD, MOVE, RESET, REVOKE, ROLLBACK, SELECT, SELECT INTO, SET, SET CONSTRAINTS, SET SESSION AUTHORIZATION, SET TRANSACTION, START TRANSACTION, TRUNCATE, UPDATE

In this article we are going to cover for ALTER USER with following points

• Introduction to ALTER USER Command
• Syntax for ALTER USER Command
• Description of ALTER USER Command
• Use of ALTER USER Command
• Example of ALTER USER Command
• Input and Output for ALTER USER Command

Introduction to ALTER USER Command

This command help us to Renames a database user or changes its default schema.

Syntax for ALTER USER Command

ALTER USER userName

     WITH <set_item> [ ,…n ]

<set_item> ::=

      NAME = newUserName

    | DEFAULT_SCHEMA = { schemaName | NULL }

    | LOGIN = loginName

    | PASSWORD = ‘password’ [ OLD_PASSWORD = ‘oldpassword’ ]

    | DEFAULT_LANGUAGE = { NONE | <lcid> | <language name> | <language alias> }

Description of ALTER USER Command

Username:

Specifies the name by which the user is identified inside this database.

LOGIN

: loginName

Re-maps a user to another login by changing the user’s Security Identifier (SID) to match the login’s SID.

NAME

: newUserName

Specifies the new name for this user. newUserName must not already occur in the current database.

DEFAULT_SCHEMA

= {schema Name | NULL }

Specifies the first schema that will be searched by the server when it resolves the names of objects for this user. Setting the default schema to NULL removes a default schema from a Windows group. The NULL option cannot be used with a Windows user.

PASSWORD

= ‘password’

Specifies the password for the user that is being changed. Passwords are case-sensitive.

OLD_PASSWORD

=‘old password’

The current user password that will be replaced by ‘password’. Passwords are case-sensitive. OLD_PASSWORD is required to change a password, unless you have ALTER ANY USER permission. Requiring OLD_PASSWORD prevents users with IMPERSONATION permission from changing the password.

DEFAULT_LANGUAGE

= { NONE | <lcid> | <language name> | <language alias> }

Specifies a default language to be assigned to the user. If this option is set to NONE, the default language is set to the current default language of the database. If the default language of the database is later changed, the default language of the user will remain unchanged. DEFAULT_LANGUAGE can be the local ID (lcid), the name of the language, or the language alias.It is used only for contained database.

Use of ALTER USER Command

The following can be achieved using the Alter user command,

• It can be used to rename username by specifying old username
• It can be used to rename default schema associated with username
• It can be used to rename collation in contained databases
• It can be used to change password in contained databases

Example of ALTER USER Command

--A. Changing the name of a database user
--First create the user:
CREATE LOGIN Mary50 
    WITH PASSWORD = 'sa';
USE AdventureWorks;
GO
CREATE USER Mary5 FOR LOGIN Mary50;
GO 

--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
CREATE LOGIN Test 
    WITH PASSWORD = 'test';
USE AdventureWorks;
CREATE USER John FOR LOGIN Test 
    WITH DEFAULT_SCHEMA = Marketing;
GO
 
--The following example changes the default schema of the user John to Purchasing. 
USE AdventureWorks;
ALTER USER John WITH DEFAULT_SCHEMA = Purchasing;
GO

  Input and Output for ALTER USER Command

For above command we can get following output sequentially

For more information  on SQL server visit any of my post

• BISQL # 99 – ‘A to Z’ SQL Command # 1 – List of Command which are present in SQL Server 2008 R2 AND SQL Server 2012
• Starting of Brand New Series – ‘A to Z’ SQL Command Series
• List of Monthly post of MS BI,SQL & Link Blog – May 2012
• BISQL # 98 – Introduction of Private Cloud with SQL Server and Hyper-V, Resource Link
• Security Guide for SAP on SQL Server 2012 – Whitepaper Released
• BISQL #97 – Laymen to SQL Developer # 9 – Assignment #2 – Relational Model, Schema, Attribute, Database, Key & Relational Query

Hope this helps and you got how to use ALTER User Command !!

Those who have not ye subscribe my Blog yet they can subscribe it !So that I can post you @ real time and all sort of knowledge in your mail without Zero spamming !!

Happy Learning and Sharing !!

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

Link Resource Website

For More information related to BI World visit my Mentalist Blog

Link Resource Blog >> Daily Interesting links

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS |