Create New Login via SQL Query in SQL Server 2008
In this article I will explain how to create login using SQL statements.
Introduction
There are various methods to create SQL Server login.
- Using SQL Server Management Studio.
- Using SQL query.
- 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:
Now refresh the LOGIN node and see new login named "NEW LOGIN" is created.