SQL Server can use Windows logons as well as logins for SQL Server. If you have configured the server for mixed security, you can use both logon types. Otherwise, you can use only Windows logons.
To view or edit an existing login, follow these steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server, and then work your way down to the Security folder.
Expand the Security folder and the Logins folder to list the current logins. Right-click a login, and then select Properties to view the properties of the login.
The Login Properties dialog box, shown in Figure 8-1, has four pages:
General. Provides an overview of the login configuration, including the authentication mode (which cannot be changed), the default database and language (which can be changed), and any mapped credentials (which can be added or removed).
Server Roles. Lists the server roles and allows you to add or remove the login’s server roles.
Database Access. Lists databases accessible by the login and allows you to manage, on a per database basis, the default schema, the user identity for the database, and the assigned database roles.
Permissions. Shows current object permissions and allows you to manage object permissions for the login.
When you are finished working with the account, click OK.
To view information about a login with Transact-SQL, use sp_helplogins. Example 8-1 shows the syntax and usage for this command.
Example 8-1. sp_helplogins Syntax and Usage
Syntax
sp_helplogins [[@LoginNamePattern =] 'login']
Usage
EXEC sp_helplogins 'goteam'
The output provided by sp_helplogins includes the login name, security identifier, default database, and default language. To determine the server roles and Windows groups to which the currently logged on user belongs either implicitly or expressly, you can execute the following query:
USE master GO SELECT * FROM sys.login_token; GO
You create new logins in SQL Server Management Studio by using the Login – New dialog box. If you want to use Windows user or group accounts, you must create these accounts on the local machine or in the Windows domain and then create the related SQL Server logins. Ask a network administrator to set up the necessary accounts.
To create a SQL Server login, follow these steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server, and then work your way down to the Security folder.
Right-click Logins, and then select New Login to display the Login – New dialog box shown in Figure 8-2.
If you are creating a logon for a Windows account, select the Windows Authentication option button, and then type the user name in DOMAINusername format, such as CPANDLwrstanek. If you want to search Active Directory for the domain and user information, click Search, and then use the Select User Or Group dialog box to select the user for which you are creating the SQL Server account. Password policy and expiration enforcement are handled by the local Windows password policy automatically.
If you want to create a new SQL Server login, select the SQL Server Authentication option button, and then type the name of the account you want to use, such as Sales or WRSTANEK. Then, enter and confirm the password for the account. To enforce the local Windows password expiration policy on the SQL Server login, select Enforce Password Policy. If you elect to enforce password policy, you can also elect to enforce password expiration. To do this, select Enforce Password Expiration.
Specify the default database and default language for the login. Assigning a default database does not give the login permission to access the database. Instead, it merely specifies the database that is used when no database is specified in a command.
Click OK to create the login. If you are creating a SQL Server login and an identically named login already exists on the server, you will see an error. Click OK and change the login, or click Cancel if you determine that the new login is not needed.
You have not yet assigned any roles or access permissions. Refer to the sections later in this chapter titled "Configuring Server Roles" and "Controlling Database Access and Administration" to learn how to configure these options.
You can also create logins with Transact-SQL. Use CREATE LOGIN as shown in Example 8-2. To use this statement, you need ALTER ANY LOGIN permission on the server (and if using credentials, you need ALTER ANY CREDENTIAL permission).
Example 8-2. CREATE LOGIN Syntax and Usage
Syntax
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 = languageUsage for SQL Logins
create login wrstanek WITH PASSWORD = 'MZ82$!408765RTM'
Usage for SQL Logins Mapped to Credentials
create login wrstanek WITH PASSWORD = 'MZ82$!408765RTM',
CREDENTIAL = StanekWRUsage for Logins from a Domain Account
CREATE LOGIN [CPANDLwrstanek] FROM WINDOWS;
Note
Although the logins sp_grantlogin and sp_addlogin allow users to connect to SQL Server, these logins cannot access databases. To configure database access, you need to run sp_grantdbaccess for each database to which the login needs access. For details, see the section titled Controlling Database Access and Administration later in this chapter.
You can edit logins in SQL Server Management Studio as explained in the section titled "Viewing and Editing Existing Logins" earlier in this chapter. Editing logins with T-SQL is more work, however, and requires you to use the ALTER LOGIN statement. You need ALTER ANY LOGIN permission to alter logins (and if working with credentials, ALTER ANY CREDENTIAL permission). When a login is a member of the sysadmin server role, only another member of this role can make the following changes:
Reset the password without supplying the old password
Enable MUST_CHANGE, CHECK_POLICY, or CHECK_EXPIRATION
Change the login name
Enable or disable the login
Change the login credential
Example 8-3 shows the syntax and usage for ALTER LOGIN.
Example 8-3. ALTER LOGIN Syntax and Usage
Syntax
ALTER LOGIN login_name
{
< status_option >
| WITH set_option [ ,... ]
}
< status_option >::=
ENABLE | DISABLE
< set_option >::=
PASSWORD = ' password '
[
OLD_PASSWORD = ' oldpassword '
| secadmin_pwd_option [ secadmin_pwd_option ]
]
| 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 | UNLOCKUsage for Changing the Login Name
ALTER LOGIN wrstanek WITH NAME = stanekwr
Usage for Changing the Login Password
ALTER LOGIN wrstanek WITH PASSWORD = '3948wJ698FFF7';
Usage for Enabling a Disabled Login
ALTER LOGIN wrstanek ENABLE;
When you create a new login or modify an existing login based on a Windows account, you can explicitly grant or deny access to the server’s database engine. Explicitly denying access to the server is useful when a particular Windows account should be temporarily restricted from accessing the server.
To grant or deny access for an existing login, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server, and then work your way down to the Security folder.
Expand the Security folder and the Logins folder to list the current logins. Right-click a login, and then select Properties to view the properties of that login. This opens the SQL Server Login Properties dialog box (shown previously in Figure 8-1).
Under Select A Page, select Status to display the Status page.
To grant access to the server, select the Grant Access option.
To deny access to the server, select the Deny Access option.
Click OK.
You can also grant or deny logins with Transact-SQL. To grant a login for a domain account, use sp_grantlogin, as shown in Example 8-4.
Note
Only members of the sysadmin or securityadmin fixed server roles can execute sp_grantlogin and sp_denylogin.
Example 8-4. sp_grantlogin Syntax and Usage
Syntax
sp_grantlogin [@loginame =] 'login'
Usage
EXEC sp_grantlogin 'GALAXYWRSTANEK'
To deny access to the server for the account, use sp_denylogin as shown in Example 8-5.
Similar to Windows accounts, SQL Server logins can be enabled and disabled by administrators. Logins can also become locked based on policy settings and may need to be unlocked. For example, if a login’s password expires, the login may become locked.
Tip You can determine whether a login is disabled or locked by selecting the server’s Logins node in SQL Server Management Studio. The icon for the login is updated to show the status as locked or disabled.
To enable, disable, or unlock a login, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server, and then work your way down to the Security folder.
Expand the Security folder and the Logins folder to list the current logins. Right-click a login, and then select Properties to view the properties of that login. This opens the SQL Server Login Properties dialog box.
Under Select A Page, select Status to display the Status page.
You can now:
Enable the login by selecting Enabled under Login.
Disable the login by selecting Disabled under Login.
Unlock the login by clearing Login Is Locked Out.
Click OK.
When a user leaves the organization or a login is no longer needed for another reason, you should remove the login from SQL Server. To remove a login, complete the following steps:
Start SQL Server Management Studio, and then access the appropriate server.
In the server’s Security folder, expand the Logins folder.
Right-click the login you want to remove, and then select Delete from the shortcut menu.
The Delete Object dialog box shows you which account you are deleting. Click OK to remove the account. Remember that you may also need to delete users in each database.
Use sp_revokelogin to delete Windows user and group accounts, as shown in Example 8-6.
Example 8-6. sp_revokelogin Syntax and Usage
Syntax
sp_revokelogin [@loginame =] 'login'
Usage
EXEC sp_revokelogin 'GALAXYWRSTANEK'
Use DROP LOGIN to remove a SQL Server login, as shown in Example 8-7.
You manage Windows user and group accounts in the Windows domain or on the local machine. Users can change their own passwords or ask the Windows administrator to reset their passwords, if necessary. For SQL Server logins, you change passwords through SQL Server Management Studio using the following steps:
Start SQL Server Management Studio, and then access the appropriate server.
In the server’s Security folder, expand the Logins folder.
Right-click the login you want to change, and then select Properties to display the SQL Server Login Properties dialog box.
Type and then confirm the new password in the boxes provided.
Click OK.
To change passwords with Transact-SQL, you can use ALTER LOGIN as discussed previously, or use sp_password, as shown in Example 8-8.
Note
Because execute permissions for sp_password default to the public role, users can change their own passwords. Members of the securityadmin and sysadmin server roles can change passwords of other logins. However, if the user is a member of the sysadmin server role, a securityadmin member must supply the old password. Members of the sysadmin role never need to supply the old password.