Home > SQL > BISQL # 103 : ‘A to Z’ SQL Command with Syntax, Description, Usage and Example # 2 : ALTER USER

BISQL # 103 : ‘A to Z’ SQL Command with Syntax, Description, Usage and Example # 2 : ALTER USER

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

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 image_thumb1CONSTRAINT 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 pointsimage

  • 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
image--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

image 

image

image

image

For more information  on SQL server visit any of my post

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 |

Categories: SQL
  1. March 3, 2013 at 4:29 am

    I comment each time I especially enjoy a post on a site
    or if I have something to contribute to the discussion.
    Usually it is a result of the fire displayed in the article
    I read. And on this post BISQL # 103 : ‘A to Z’ SQL Command with Syntax,
    Description, Usage and Example # 2 : ALTER USER | SQL Server Mentalist.

    I was moved enough to drop a thought :-P I do have 2 questions
    for you if it’s okay. Could it be simply me or does it appear like a few of these comments appear like they are left by brain dead people? :-P And, if you are posting on additional social sites, I’d like to keep up with everything
    fresh you have to post. Would you make a list all of your communal sites like your Facebook page, twitter feed, or linkedin profile?

  1. July 23, 2012 at 6:15 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,120 other followers

%d bloggers like this: