Managing SQL Server Logins

Adding, dropping, or modifying logins can be easily accomplished using Enterprise Manager or T-SQL. The tool you choose depends on your preference, but I usually reach for Enterprise Manager when dealing with one or two logins, and use T-SQL to script multiple login additions or changes. A wizard is also accessible through the Magic Wand icon on the toolbar to step you through the process.

Managing Logins with Enterprise Manager

Logins are managed through the Security folder in Enterprise Manager. Right-clicking Logins and selecting New Login brings up the General tab of the Login dialog box, as shown in Figure 15.4.

Figure 15.4. Creating a login in Enterprise Manager.


If you are creating a standard SQL Server login, enter a name, select SQL Server Authentication, and enter a password. If the login is to be associated with a Windows user or group, clicking the Ellipse button (…) beside the Name field will bring up a list box where you can select the domain and associated account. Alternatively, you can type the name and select the domain from the domain drop-down box. The account must be an existing domain user or group. From this page, you can also grant or deny access to the login and choose its default database and language.

To provide one-stop shopping, two other tabs are also available. The first tab, Server Roles, allows you to select which, if any, fixed server roles the login should belong to, as illustrated in Figure 15.5.

Figure 15.5. Choosing a server role.


The Database Access tab allows you to select the databases to which the login should have access and to select the name of the user in the database. This actually creates the user in the database for you, which is a nice feature. If desired, you can also select any roles to which the user should belong. The Database Access tab is shown in Figure 15.6.

Figure 15.6. The Database Access tab.


If changes need to be made to an existing login, double-clicking the login in the Details pane will access the Logins Properties page again, allowing changes to all but the login name. To delete logins, highlight the login(s) in the Details pane and press the Delete key. You will be asked if you want to proceed; selecting Yes will delete the login and any associated database users.

Managing Logins with T-SQL

Managing logins through T-SQL is not as user-friendly as with Enterprise Manager; however, it does provide a way to script multiple logins and incorporate some functionality not available in the GUI. Table 15.3 lists the stored procedures used to manage logins.

Table 15.3. Stored Procedures for Managing Logins
Stored Procedure Function
sp_addlogin Add a SQL Server login
sp_grantlogin Add a Windows login
sp_droplogin Drop a SQL Server login
sp_revokelogin Drop a Windows login
sp_denylogin Deny access to a Windows login
sp_password Change a login's password
sp_defaultdb Change the default database
sp_defaultlanguage Change the default language

Each of these stored procedures requires input arguments associated with the action being performed, as in the following example, which creates a new SQL Server login "Sue" with a password of "fido" and "Pubs" as the default database:

EXEC sp_addlogin 'Sue' , 'fido', 'Pubs' 

If you were adding a Windows login, the syntax would be as follows:

EXEC sp_grantlogin 'domain1\Sue', 

The input arguments vary depending on the procedure and are well documented in Books Online, but it is worth mentioning a few that perform functions not available through Enterprise Manager. Sp_addlogin has two input arguments?@sid and @encryptopt?to facilitate migrating logins to another SQL Server.

The @sid argument accepts a 16-byte SID, or security identifier, which will be the SID of the newly created login. This allows you to migrate logins to another server while keeping the same security identifier. The default is NULL, which causes SQL Server to generate a new SID.

The @encryptopt argument accepts three values: NULL, SKIP ENCRYPTION, and SKIP ENCRYPTION OLD. The default NULL causes the password to be encrypted. If SKIP_ENCRYPTION is used, it is assumed you are providing an already encrypted password. This encrypted password can be obtained from the sysxlogins table of the server from which you are migrating logins, and allows the logins on the new server to retain their old password. The SKIP_ENCRYPTION_OLD value is used during upgrade and specifies that the password was encrypted by a previous version of SQL Server.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features