Working with SQL Server Logins

In addition, I wrote this article in my blog, Just Like a Magic.

Overview

This lesson discusses all the details of SQL Server logins. It begins by discussing how to create SQL Server logins. After that, it focuses on how to change the properties of existing login. Next, it discusses how to delete an existing login. Moreover, we will focus on how to enumerate a list of existing logins and roles. Lastly, we will talk a look on how to manage login permissions in SQL Server. In addition, we will link between SQL Server and .NET Framework and we will teach you many techniques other than what this lesson is focusing on.

Table of Contents

This is the table of contents of this lesson:

• Overview
• Table of Contents
• Introduction
• Creating a SQL Server Login
  • Creating a Login via SQL Server Management Studio
  • Creating a Login via the CREATE LOGIN T-SQL Statement
    • Creating a Login from a Windows Domain Account
    • Creating a Login Specific to SQL Server
  • Creating a Login via a System Stored Procedure
  • Creating a Login via .NET
• Using the Login to Access SQL Server
  • Accessing SQL Server via SQL Server Management Studio
  • Accessing SQL Server via .NET
• Changing an Existing Login
  • Changing a Login via SQL Server Management Studio
  • Changing a Login via the ALTER LOGIN T-SQL Statement
    • Changing the Name and Password of a Login
    • Enabling/Disabling a Login
  • Changing a Login via .NET
• Deleting an Existing Login
  • Deleting a Login via SQL Server Management Studio
  • Deleting a Login via the DROP LOGIN T-SQL Statement
  • Deleting a login via a System Stored Procedure
  • Deleting a Login via .NET
• Enumerating Existing Logins
• Working with Permissions
  • Changing User Permissions via Login Properties Dialog
  • Changing User Permissions via Server Properties Dialog
• References
• Summary

Introduction

Today, we are concentrating on how to work with SQL Server logins. A login is simply credentials for accessing SQL Server. For example, you provide your username and password when logging on to Windows or even your e-mail account. This username and password build up the credentials. Therefore, credentials are simply a username and a password.

You need valid credentials to access your SQL Server instance or even to access a database from a .NET application for instance. Like Windows credentials, SQL Server uses multiple credentials to secure each of its granules differently from the other. For example, Windows links the user’s identity with multiple roles. Therefore, user is allowed to access only the resources that are allowed for him based on his identity and roles. In addition, using ACL you can limit access to some system resources and allow others. SQL Server on the other hand uses credentials to manage what the user is allowed to do with SQL Server. For instance, a specific user may not be allowed to access the Northwind database or even to modify it only.

For those reasons and more, we decide to discuss in this lesson how to work with SQL Server logins.

SQL Server allows four types of logins:

1. A login based on Windows credentials.
2. A login specific to SQL Server.
3. A login mapped to a certificate.
4. A login mapped to asymmetric key.

Actually, we are interested in only logins based on Windows Credentials and logins specific to SQL Server. For more information about mapped logins, consult MSDN documentation.

Logins based on Windows credentials, allows you to log in to SQL Server using a Windows user’s name and password. If you need to create your own credentials (username and password,) you can create a login specific to SQL Server.

Creating a SQL Server Login

To create, alter, or remove a SQL Server login, you can take one of three approaches:

1. Using SQL Server Management Studio.
2. Using T-SQL statements.
3. Using system stored procedures.

Of course, you can combine the last two approaches with either the SQL Server IDE or through .NET code.

If you are using SQL Server Express, you can skip the first way that creates the login using the SQL Server Management Studio.

Creating a Login via SQL Server Management Studio

To create a login via the SQL Server Management Studio, follow those steps:

1. Open the SQL Server Management Studio.
2. Connect to the Database Engine.
3. From the Object Explorer step down to the Security object node then to the Logins node.
4. Right-click the Logins node and choose New Login. This brings up the New Login dialog. Figure 1 shows SQL Server Management Studio while in the Logins view.
   
5. In the New Login dialog, select your required login type and enter the required information. If you want to create a login from a Windows domain account, select “Windows authentication” and enter the domain name and account name in the “Login name” field. You may use the Search button to determine if the name exists. It is worth mentioning that you can add a Windows domain group login too not a user only. If you want to create a login specific to SQL Server, select “SQL Server authentication” and enter your desired username and password. Be sure to review the password settings. In addition, from the page General you can select the default database and language for the new login. Furthermore, you can edit user’s privileges at a more granular level from other pages like Server Rules, User Mapping, and Status. The last section of this lesson is devoted for this. More help in the MSDN documentation. Figure 2 shows the New Login dialog while adding the new SQL Server login.
   
6. Click OK. Your new login is now available and ready for use.

What about” Mapped to certificate“ and “Mapped to asymmetric key” logins? Actually, these logins cannot be created through New Login window. However, you can create it through the CREATE LOGIN statement which is covered soon. However, we would not cover these two types. Therefore, it is useful checking MSDN documentation.

What about security settings in the New Login dialog? Enforcing password policy means enforcing password rules like password complexity requirement and password length. Enforcing password expiration means that the user will be required to change his password from time to time every a specific period specified by SQL Server. In addition, you can require the user to change his password the next time he logs on to SQL Server. Notice that the three settings are related to each other. For example, disabling the enforcement of password policy disables other settings. It is worth mentioning that these settings are only available for logins specific to SQL Server only.

Creating a Login via the CREATE LOGIN T-SQL Statement

Another way to create a new login is through the CREATE LOGIN T-SQL statement. The syntax of this statement is as following:

CREATE LOGIN login_name
{ WITH <option_list1> | FROM <sources> }<sources> ::=
WINDOWS [ WITH <windows_options> [ ,... ] ]
| CERTIFICATE certname
| ASYMMETRIC KEY asym_key_name

<option_list1> ::=
PASSWORD = 'password' [ HASHED ] [ MUST_CHANGE ]
[ , <option_list2> [ ,... ] ]

<option_list2> ::=
SID = sid
| DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
| CHECK_EXPIRATION = { ON | OFF}
| CHECK_POLICY = { ON | OFF}
[ CREDENTIAL = credential_name ]

<windows_options> ::=
DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language

Actually this statement can be used to create a login of any type. However, because of the needs of our lesson, we will focus on how to create logins based on Windows domain accounts -and groups- and logins specific to SQL Server.

Creating a Login from a Windows Domain Account

Now we are going to create the login “Mohammad Elsheimy” which is based on the user account “Mohammad Elsheimy” which exists on the machine “BillGates-PC”. In addition, we will change the default database to Northwind and the default language to English.

CREATE LOGIN [BillGates-PC\Mohammad Elsheimy] FROM WINDOWS
WITH DEFAULT_DATABASE=[Northwind], DEFAULT_LANGUAGE=[English];

Actually, changing default database and default language is optional. Therefore, you can omit both or only one.

If you need to create a login of the Windows domain group change the login name to the group name.

When working with T-SQL statements, be sure to refresh the Logins node after executing your T-SQL statement to see your new baby.

If you are using SQL Server Express, of course you would not find SQL Server Management Studio to execute the commands. However, you can execute these commands via .NET which is covered soon.

Some T-SQL statements -and stored procedure- require the current login to have some privileges to execute them. If you faced a security problem executing any of the T-SQL statements found here, check the MSDN documentation for more help about the required permissions. Although, be sure to check the last section of this lesson.

Creating a Login Specific to SQL Server

Now we are going to create the login “My Username” with the password “buzzword”. In addition, this user will have to change his password at the next logon. Moreover, password policy and expiration are turned on. Furthermore, default database is set to Northwind and default language is set to English.

CREATE LOGIN [My Username] WITH PASSWORD=N'buzzword'
MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON,
DEFAULT_DATABASE=[Northwind], DEFAULT_LANGUAGE=[English];

Again, changing default database and default language is optional. In addition, explicitly setting password settings is optional too; you can omit one of them or all of them. However, if you omit the password policy enforcement setting, it is still turned on. If you want to turn it off, set it explicitly.

Creating a Login via a System Stored Procedure

You can create a new login via the system stored procedure sp_addlogin. The definition of this stored procedure is as following:

sp_addlogin [ @loginame = ] 'login'
    [ , [ @passwd = ] 'password' ]
    [ , [ @defdb = ] 'database' ]
    [ , [ @deflanguage = ] 'language' ]
    [ , [ @sid = ] sid ]
    [ , [ @encryptopt= ] 'encryption_option' ]

This stored procedure accepts the following arguments:

• @loginname:
  The name of the login. In other words, the username.
• @passwd:
  Optional. The password of the login. Default is NULL.
• @defdb:
  Optional. The default database. Default is MASTER.
• @deflanguage:
  Optional. The default language. Default is NULL which means <default>.
• @sid:
  Optional. Used to set a Security Identifier (SID) for the new login. If NULL, SQL Server automatically generates one. Default is NULL. Notice that if this is a Windows domain user -or group- login, this argument is automatically set to the Windows SID of this user -or group.-
• @encryptopt:
  Optional. Default is NULL. Specifies whether the password is set as plain text or hashed. This argument can take one of three values:
  • NULL:
    Specifies that the password is set as clear (plain) text.
  • skip_encryption:
    Specifies that the password is already hashed. Therefore, the Database Engine would store the database without hashing it.
  • skip_encryption_old:
    Specifies that the password is already hashed but with an earlier version of SQL Server. Therefore, the Database Engine would store the database with our re-hashing it. This option is used for compatibility purposes only.

This function returns 0 if succeeded or 1 otherwise.

Here is an example creates again the login “My Username” that was created earlier.

EXEC sys.sp_addlogin  N'My Username', N'buzzword';

As you might expect, you will receive an error if you tried to execute the last line while a login with the same name exists.

Creating a Login via .NET

As you know, you can execute any SQL Server command through the SqlCommand object. Therefore, we are going to combine the last two approaches for creating the login with C#. We will create the login programmatically via .NET and C# through our T-SQL statements. Here is the code:

// Connecting to SQL Server default instance
// to the default database using current
// Windows user's identity.
// The default database is usually MASTER.
SqlConnection conn = new SqlConnection
    ("Data Source=;Initial Catalog=;Integrated Security=True");

// Creating a login specific to SQL Server.
SqlCommand cmd = new SqlCommand
    ("CREATE LOGIN [My Username] WITH PASSWORD=N'buzzword' " +
    "MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON, " +
    "DEFAULT_DATABASE=[Northwind], DEFAULT_LANGUAGE=[English];",
    conn);
// In addition, you can use this command:
// EXEC sys.sp_addlogin  N'My Username', N'buzzword'
// Moreover, you can set the command type to stored procedure,
// set the command to sys.sp_addlogin,
// and add parameters to the command to specify the arguments.

try
{
    conn.Open();
    cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
    if (ex.Number == 15025)
        Console.WriteLine("Login already exists.");
    else
        Console.WriteLine("{0}: {1}", ex.Number, ex.Message);
}
finally
{
    cmd.Dispose();
    conn.Close();
}

Notice the numbers assigned to errors.

Using the Login to Access SQL Server

Here, we will cover the two techniques for accessing SQL Server. We will cover accessing SQL Server through its IDE (SQL Server Management Studio) and accessing SQL Server programmatically via .NET.

If you are using SQL Server Express, you can step the SQL Server Management Studio section and dive directly into .NET.

Accessing SQL Server via SQL Server Management Studio

After you start SQL Server Management Studio, you face the “Connect to Server” dialog that allows you to connect to SQL Server using valid credentials (login in other words.) Figure 3 shows the “Connect to Server” dialog.

If you cancelled this dialog, you will not be able to connect to the server. However, as you know, you can connect again through either the Object Explorer window or File->Connect Object Explorer command.

From this dialog, you can choose between two types of authentication:

1. Windows Authentication:
   Logs on to the server with the credentials of the current user.
2. SQL Server Authentication:
   Logs on to the server with credentials specific to SQL Server. You may provide your username as password to access SQL Server. It is worth mentioning that the administrator user “sa” is also specific to SQL Server.

Accessing SQL Server via .NET

In .NET, you can access SQL Server through another login the same way as you do while accessing it via the “sa” login but with changing the username and password to the new information.

The following example shows how to access SQL Server through Windows authentication.

// Connecting to SQL Server default instance
// to the default database using current
// Windows user's identity.
// The default database is usually MASTER.
SqlConnection conn = new SqlConnection
    ("Data Source=;Initial Catalog=;Integrated Security=True");

try
{
    conn.Open();
    // Connected
}
catch (SqlException ex)
{
    Console.WriteLine("{0}: {1}", ex.Number, ex.Message);
}
finally
{
    conn.Close();
}

Now, it is the time for the code that accesses SQL Server through SQL Server authentication. This code tries to log-in to SQL Server via our newly created login “My Username.” You can specify your username and password via the User ID (or UID) and Password (or PWD) settings in the connection string.

// Connecting to SQL Server default instance
// to the default database using the user
// "My User" and his password "buzzword"
// The default database is usually MASTER.
SqlConnection conn = new SqlConnection
    ("Data Source=;Initial Catalog=;" +
    "User ID=My Username;Password=buzzword");
try
{
    conn.Open();
    // Connected
}
catch (SqlException ex)
{
    if (ex.Number == 18456)
        Console.WriteLine("Bad username or password.");
    else
        Console.WriteLine("{0}: {1}", ex.Number, ex.Message);
}
finally
{
    conn.Close();
}

Notice that if you created the user with MUST_CHANGE setting specified, you will not be able to access SQL Server with this user unless you change his password. Notice the error number returned. 

Changing an Existing Login

While you can create a new login using SQL Server Management Studio, T-SQL statements, or stored procedures, you cannot change (alter) your login using the third way. Therefore, you have only two ways to change your new login, either using SQL Server Management Studio or using the ALTER LOGIN T-SQL statement.

Changing a Login via SQL Server Management Studio

To change a login via SQL Server Management Studio, step down to the Logins node in the Object Explorer then double-click your login to show the Login Properties dialog. This dialog is very similar (yet identical) to the New Login dialog; it is used to change the login properties. Figure 4 shows the Login Properties dialog.

Actually, if this is a Windows authentication login, you would not be able to change any information in the General page except the default database and default language. However, you can change other characteristics in the other tabs.

If this is a SQL Server authentication login, you are allowed only to change the password besides the default database and default language. However, as with Windows authentication logins, you can change other properties in the other tabs.

Changing a Login via the ALTER LOGIN T-SQL Statement

What if you need more control over the changing process? What if you need to alter (change) other mapped logins? The answer is you can do this via the T-SQL statement ALTER LOGIN. The syntax of this statement is as following:

ALTER LOGIN login_name
{
<status_option>
| WITH <set_option> [ ,... ]
}

<status_option> ::=
ENABLE | DISABLE

<set_option> ::=
PASSWORD = 'password'
[
OLD_PASSWORD = 'oldpassword'
| <secadmin_pwd_opt> [ <secadmin_pwd_opt> ]
]
| DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
| NAME = login_name
| CHECK_POLICY = { ON | OFF }
| CHECK_EXPIRATION = { ON | OFF }
| CREDENTIAL = credential_name
| NO CREDENTIAL

<secadmin_pwd_opt> ::=
MUST_CHANGE | UNLOCK

Changing the Name and Password of a Login

While you cannot change the name of a user via the Login Properties dialog, you can do this through the ALTER LOGIN statement. The following T-SQL statement changes our user “My Username” to be “someuser” and changes his password too to be “newbuzzword”.

ALTER LOGIN [My Username]
	WITH NAME = [someuser] , PASSWORD = N'newbuzzword';

As a refresher, some T-SQL statements require special permissions. If you faced a security problem executing statements found here, then you are not authorized. Check the MSDN documentation for more help about permissions required.

Again, if you do not have SQL Server Management Studio, you can use the Server Explorer in Visual Studio .NET.

Enabling/Disabling a Login

The following statement disables the login “someuser” so that nobody can access it.

ALTER LOGIN [someuser] DISABLE;

To get your login back, change the DISABLE keyword to the ENABLE keyword.

Changing a Login via .NET

As we did earlier, you can use the SqlCommand object combined with a SqlConnection object to execute T-SQL statements against your database. The following code segment disables a login and tries to login to SQL Server using it.

// Log in using Windows authentication
SqlConnection conn = new SqlConnection
    ("Data Source=;Initial Catalog=;Integrated Security=True");
SqlCommand cmd =
    new SqlCommand("ALTER LOGIN [someuser] DISABLE;", conn);

try
{
    conn.Open();
    // Connected
    cmd.ExecuteNonQuery();
    // Succeeded
    conn.Close();

    // Another technique to create your connection string
    SqlConnectionStringBuilder builder =
        new SqlConnectionStringBuilder(conn.ConnectionString);
    // Use this line to remove the Windows auth keyword
    // builder.Remove("Integrated Security");
    // Or else, set Windows authentication to False
    builder.IntegratedSecurity = false;
    builder.UserID = "someuser";
    builder.Password = "newbuzzword";

    conn.ConnectionString = builder.ToString();

    // The following line would raise the error 18470
    conn.Open();
    // Connected
    conn.Close();
    // Closed
}
catch (SqlException ex)
{
    if (ex.Number == 18470)
        Console.WriteLine("Account disabled.");
    else
        Console.WriteLine("{0}: {1}",
            ex.Number, ex.Message);
}
finally
{
    cmd.Dispose();
    conn.Close();
}

Notice that new technique of building connection string; it is using the ConnectionBuilder object.

Deleting an Existing Login

Like creating a new login, deleting (dropping) an existing login can be done through a way of three:

1. Using the SQL Server Management Studio.
2. Using T-SQL Statements.
3. Using System Stored Procedures.

Again, you can combine either the second or the last way with .NET.

If you are using SQL Server Express, you can skip the first way that creates the login using the SQL Server Management Studio.

Deleting a Login via SQL Server Management Studio

Form SQL Server Management Studio, step down to the logins object in the Object Explorer and select your login. From the context menu of the login, you can select Delete to delete the login.

Deleting a Login via the DROP LOGIN T-SQL Statement

To remove a login from SQL Server using a T-SQL statement, you can use the DROP LOGIN statement. The following is the syntax for this statement:

DROP LOGIN login_name

As you know, to delete the login “someuser”, use the following example:

DROP LOGIN [someuser];

You cannot delete a user that already logged on.

This way is used for all types of logins. Just specify the login name.

Deleting a login via a System Stored Procedure

The system stored procedure sp_droplogin is the procedure responsible for dropping an existing login. The definition of this function is as following: 

sp_droplogin [ @loginame = ] 'login'

This function accepts only a single argument which is the name of the login. This is the T-SQL statement that deletes the login “someuser”.

EXEC sp_droplogin 'someuser';

Again and again, some T-SQL statements and procedures require special permissions. Check MSDN for details. In addition, the last section devoted for this.

Deleting a Login via .NET

As we said earlier, you can execute a T-SQL statement or stored procedure in .NET via the SqlCommand and SqlConnection object. Here is the code that deletes the login “someuser”.

SqlConnection conn = new SqlConnection
    ("Server=.;Data Source=;UID=someuser;PWD=newbuzzword");

SqlCommand cmd = new SqlCommand
    ("DROP LOGIN [asomeuser];", conn);
// In addition, you can use this command:
// EXEC sp_droplogin 'someuser';

try
{
    conn.Open();
    cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
    if (ex.Number == 15151)
        Console.WriteLine("Login does not exist.");
    else if (ex.Number == 15007)
        Console.WriteLine("Login already logged on.");
    else
        Console.WriteLine("{0}: {1}", ex.Number, ex.Message);
}
finally
{
    conn.Close();
}

Enumerating Existing Logins

You can enumerate existing logins through the system table server_principals. This table encapsulates SQL Server principals’ information such as logins and roles. The following query statement retrieves all data from the sys.server_principals table.

SELECT * FROM sys.server_principals
	ORDER BY type, [name];

Figure 5 shows a snapshot of the results on my SQL Server instance.

From the names of column names you can know what every column stores. However, server_principals encapsulates much security data that we are not interested in. For example, it encapsulates roles information that we are not interested in here. The columns type and type_desc both specifies data type. The type column specifies the type while the type_desc stores a simple description of that type.

The “type” column could store several values including:

• R:
  Specifies that the data is for a SERVER_ROLE which means that the data is for SQL Server role.
• U:
  Specifies that the data is for a login of the type WINDOWS_LOGIN which means that the data is for a Windows domain account -based login.
• G:
  Specifies that the data is for a login of the type WINDOWS_GROUP which means that the data is for a Windows domain group -based login. For example, a login based on the Windows domain group Administrators.
• S:
  Specifies that the data is for a login of the type SQL_LOGIN which means that the data is for a SQL Server -specific user.
• C:
  Specifies that the data is for a login of the type CERTIFICATE_MAPPED_LOGIN which means that the data is for a login mapped to a certificate.
• K:
  Specifies that the data is for a login of the type ASYMMETRIC_KEY_MAPPED_LOGIN which means that the data is for a login mapped to an asymmetric key.

It is worth mentioning that the column is_disabled specifies whether the account is disabled (which equals 1,) or enabled (which equals 0.)

Worth mentioning too that the column sid specifies the SID (Security Identifier) of the login. If this is a Windows principal (user or group,) it matches Windows SID. Otherwise, it stores the SID created by SQL Server to that login (or role.)

Working with Permissions

We have faced many times permission problems. Here, we will talk about how to work with permissions and to grant or deny a user a specific permission.

You have seen many times how the user can be prevented from executing some T-SQL statements due to his privileges. You can change a user’s permission from many places including Login Properties dialog and Server Explorer dialog.

Changing User Permissions via Login Properties Dialog

Take a second look at the Properties dialog of the login. You might notice that other property pages exist such as Server Roles, User Mapping, Securables, and Status pages. Take your time playing with these settings and do not forget to check MSDN documentation.

Changing User Permissions via Server Properties dialog

Another way to change user permissions is through the Server Properties dialog. You can right-click the server and choose Properties to open the Server Properties dialog. Figure 6 shows the Server Properties dialog showing the Permissions page.

As you might think, changing the permissions done through the Permissions dialog. It is worth mentioning that all of those options can be changed through the Securable page of the Login Properties dialog. However, here you can change permissions for many logins -or roles- easily. But with Login Properties dialog, you need to change every login separately.

Notice that, from this page you can change permissions for a login -or role- explicitly. Which means that if you did not change that permission explicitly it (the login or role,) will get that permission from another membership that it belongs to. For example, if you did not specify explicitly the permission “Alter any login” for the Windows user Administrator login, he will get that permission from -for instance- the Windows group Administrators login. If that login denied the permission, then the Administrator login would be denied. You can call this technique “Intersection” as with Windows ACLs.

Take a time playing with the Server Properties dialog especially in the Security and Permissions pages. And be sure to have the MSDN documentation with your other hand.

References

If you want more help, it is always helpful to check the MSDN documentation. It is useful using the search feature to search for a T-SQL statement or for help on a dialog or window.

Summary

At the end of this lesson, you learned many techniques about SQL Server and .NET. You learned all the bits of logins and how to deal with them. Plus, you learned many .NET techniques that help you coding better.

Have a nice day…