 |
Database »
Database »
SQL Server
Intermediate
License: The Common Public License Version 1.0 (CPL)
Working with SQL Server LoginsBy Mohammad ElsheimyLearn the details of how to work with SQL Server logins. |
C# (C# 1.0, C# 2.0, C# 3.0, C# 4.0), SQL, .NET (.NET 1.0, .NET 1.1, .NET 2.0, .NET 3.0, .NET 3.5, .NET 4.0), SQL Server (SQL 2000, SQL 2005, SQL CE, SQL 2008), Visual Studio, ADO.NET, DBA
|
||||||||||||
|
Advanced Search Add to IE Search |
|
|
|
||||||||||||||||
In addition, I wrote this article in my blog, Just Like a Magic.
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.
This is the table of contents of this lesson:
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:
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.
To create, alter, or remove a SQL Server login, you can take one of three approaches:
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.
To create a login via the SQL Server Management Studio, follow those steps:
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.
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.
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.
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.
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:
NULL. MASTER. 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.- NULL. Specifies whether the password is set as plain text or hashed. This argument can take one of three values:
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.
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.
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.
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:
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.
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.
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.
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
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
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.
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.
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.
Like creating a new login, deleting (dropping) an existing login can be done through a way of three:
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.
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.
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.
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.
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();
}
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:
SERVER_ROLE which means that the data is for SQL Server role. WINDOWS_LOGIN which means that the data is for a Windows domain account -based login. 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. SQL_LOGIN which means that the data is for a SQL Server -specific user. CERTIFICATE_MAPPED_LOGIN which means that the data is for a login mapped to a certificate. 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.)
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.
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.
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.
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.
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…
| You must Sign In to use this message board. | |||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||
General 
News 
Question 
Answer 
Joke 
Rant 
Admin
|
PermaLink |
Privacy |
Terms of Use
Last Updated: 18 Jun 2009 Editor: |
Copyright 2009 by Mohammad Elsheimy Everything else Copyright © CodeProject, 1999-2009 Web22 | Advertise on the Code Project |
