---
title: "Create User Login in SQL Server 2008 R2"  
description: "Create user with the query                                         CREATE LOGIN [Login_Name] WITH PASSWORD='password' MUST_CHANGED, DEFAULT_DATABASE=["  
author: "Vijay Shukla"  
published: 2012-12-04  
updated: 2014-09-18  
canonical: https://www.mindstick.com/blog/401/create-user-login-in-sql-server-2008-r2  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# Create User Login in SQL Server 2008 R2

##### Create user with the query

```
CREATE LOGIN [Login_Name] WITH PASSWORD='password' MUST_CHANGED, DEFAULT_DATABASE=[your_database], DEFAULT_LANGUAGE= [language],CHECK_EXPIRATION={OFF/ON}, CHECK_POLICY={OFF/ON}
```

Login_Name:- Specifies the name of the login that is created.

[PASSWORD](https://www.mindstick.com/blog/118/retriving-user-password-by-using-stored-procedure-in-asp-dot-net): - Applies to [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server) logins only. Specify the password for the login that is being created.

MUST_CHANGED:- If this [option](https://www.mindstick.com/forum/159391/jquery-get-selected-option-from-dropdown) is included, SQL Server [prompts](https://yourviews.mindstick.com/view/88552/best-claude-prompts-for-software-engineers) the user for a new password the first time the new login is used.

DEFAULT_DATABASE:- Specifies the [default](https://www.mindstick.com/interview/12771/what-is-the-importance-of-default-resources) [database](https://www.mindstick.com/articles/12226/use-of-database-in-sencha-extjs-and-insert-record-from-user-form-using-ajax) to be assigned to the login. If this option is not included, the default database is set to master.

DEFAULT_LANGUAGE:- Specifies the default [language](https://www.mindstick.com/startup/28/preply-the-fast-growing-platform-transforming-language-learning) to be assigned to the login.

CHECK_EXPIRATION:- Specifies whether password expiration [policy](https://www.mindstick.com/articles/157184/check-lic-policy-status-online-by-policy-number) should be enforced on this login. By default it’s OFF.

CHECK_POLICY:- Specifies that the [Windows](https://www.mindstick.com/articles/311752/how-to-install-and-use-the-google-wifi-software-on-a-windows-or-mac-computer) password policies of the computer on which SQL Server is running should be enforced on this login. By default it’s ON.

##### Create login User through Procedure

```
EXEC sp_addlogin ‘login_name','password','Default_database','default_language'
```

##### Add Server Role in Login User:-

```
EXEC sp_addsrvrolemember [@loginame=] 'login', [@rolename =] 'role'
```

##### Role Names:-

##### I. sysadmin

##### \
II. securityadmin

##### \
III. serveradmin

##### \
IV. setupadmin

##### \
V. processadmin

##### \
VI. diskadmin

##### \
VII. dbcreator

##### \
VIII. bulkadmin

##### IX. public

##### \
Create Role member permission:-

\

```
CREATE ROLE role_nameGRANT SELECT,INSERT,UPDATE,DELETE  ON dbo.Table TO role_name;role_name:- Specifies name of the ROLE which is created.dbo.Table:- Specifies the Table object name.
```

##### Drop Role:-

```
  DROP ROLE role_name
```

##### Give the permission for Login User:-

```
  GRANT create permission to login_name
```

##### Permission:-

##### \
1) ADMINISTER BULK OPERATIONS

##### 2) ALTER ANY CONNECTION

##### 3) ALTER ANY CREDENTIAL

##### 4) ALTER ANY DATABASE

##### 5) ALTER ANY ENDPOINT

##### 6) ALTER ANY EVENT NOTIFICATION

##### 7) ALTER ANY LINKED SERVER

##### 8) ALTER ANY LOGIN

##### 9) ALTER ANY SERVER AUDIT

##### 10)ALTER RESOURCES

##### 11)ALTER SERVER STATE

##### 12)ALTER SETTINGS

##### 13)ALTER TRACE

##### 14)AUTHENTICATE SERVER

##### 15)CONNECT SQL

##### 16)CONTROL SERVER

##### 17)CREATE ANY DATABASE

##### 18)CREATE DDL EVENT NOTIFICATION

##### 19)CREATE ENDPOINT

##### 20)CREATE TRACE EVENT NOTIFICATION

##### 21)EXTERNAL ACCESS ASSEMBLY

##### 22)SHUTDOWN

##### 23)UNSAFE ASSEMBLY

##### 24)VIEW ANY DATABASE

##### 25)VIEW ANY DEFINITION

##### \
26)VIEW SERVER STATE

##### Example:-

```
    GRANT create ANY DATABASE to mylogin( Now in mylogin we can create any database).
```

---

Original Source: https://www.mindstick.com/blog/401/create-user-login-in-sql-server-2008-r2

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.