Create New Login via SQL Query in SQL Server 2008

In this article I will explain how to create login using SQL statements.
  • 1841

Introduction

There are various methods to create SQL Server login.

  1. Using SQL Server Management Studio.
  2. Using SQL query.
  3. Using system stored procedure

In my previous article I explained about how to create new user via SQL Server Management Studio. Now in this article I will explain how to create new login via SQL query.

Creating a Login via SQL Statement

There is a another way to create login by using CREATE LOGIN Sql statement.

Syntax

CREATE LOGIN [LOGIN _NAME] WITH PASSWORD=N'PASSWORD'

MUST_CHANGE,

CHECK_EXPIRATION={OFF/ON},

DEFAULT_DATABASE=[DATABASE_NAME],

CHECK_POLICY={OFF/ON},

DEFAULT_LANGUAGE=[LANGUAGE];

 

where,

LOGIN_NAME- specifies the login name that is to be created.

PASSWORD- specifies the password for the new login to be created.

MUST CHANGE- If this option is included then user will have to change his password at the first time the new login is used.

CHECK_EXPIRATION- specifies password expiration policy. By default its remains OFF.

DEFAULT DATABASE- specifies default database to which user is allowed to access. If this option is not included, the user is allowed to access the master database.

DEFAULT_LANGUAGE- specifies default language.

Example

 So now we are creating the new login "NEW LOGIN" with the password "sqlserver123@".

CREATE LOGIN [NEW LOGIN] WITH PASSWORD=N'sqlserver123@'

MUST_CHANGE,

CHECK_EXPIRATION=ON,

DEFAULT_DATABASE=[mcn],

CHECK_POLICY = ON,

DEFAULT_LANGUAGE=[English];

Output:

command executed successfully.jpg

Now refresh the LOGIN node and see new login named "NEW LOGIN" is created.

  login created.jpg 

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.