Generating New SQL Server Logins

If your organization has the kind of revolving-door staff turnover that many businesses are facing, you create new logins frequently. So how do you create a new SQL Server login that has the same permissions as an existing login? Each time you create a new SQL Server login that mirrors the permissions of an existing login, you probably go through a manual process in Enterprise Manager of reviewing the existing login's permissions, then granting the appropriate SQL Server access to the new login. This manual analysis and creation process can be tedious and error-prone. Have you ever considered auto-mating this administrative function?

I suffered with the manual permissions-analysis and login-creation process many times. After about the hundredth time, I decided to automate the process. Automation eliminates the manual analysis and reduces the time I spend adding new logins. Automation also eliminates errors and the problems associated with missing or dropped permissions. Now, I never incorrectly set up permissions for a new login. Additionally, after I automated this process, my customers (my coworkers at the Washington State Department of Health--DOH) no longer needed to have an intimate knowledge of specific permissions when requesting access for a new login. Instead, they needed only to identify an existing login that had the correct permissions, so the login-permission—request process became easier for my customers and me.

I streamlined this mundane task by writing a stored procedure called USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN, which Web Listing 1 shows. (You can download the complete Web listing using the "Download the Code" link at the top of the page.) This stored procedure searches through the SQL Server permissions associated with the existing login and generates the T-SQL commands to add a new login that has the same permissions as the existing login. The stored procedure works with SQL Server 2000 and 7.0 and with both SQL Server and Windows NT authentication.

The stored procedure uses the sp_help-user, sp_helpsrvrolemember, and sp_helprotect system stored procedures to gather login permission information from the SQL Server system tables. The stored procedure uses sp_helpuser to identify the databases and the database roles that the existing login can access, it determines the server roles associated with the existing login by using sp_helpsrvrolemember, and it uses sp_helprotect to identify object-level permissions associated with the existing login.

I use Query Analyzer to execute this stored procedure; when the stored procedure finishes, Query Analyzer displays the T-SQL commands required to add the new login with the appropriate permissions. Let's look at how this code uses system stored procedures to grant server and database rights to a new login.

Stored Procedure Details

USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN requires a few input parameters that identify the existing and new logins. The stored procedure accepts four input parameters: @OLDLOGIN, @NEWLOGIN, @NEWUSER, and @PASSWORD. The first three parameters are required, but @PASSWORD is optional depending on the type of login you're adding. The @OLDLOGIN parameter identifies the existing login that you want to clone, and @NEWLOGIN identifies the login that you want to create. The @NEWUSER parameter identifies the name of the database user that will be associated with the new login when you grant the login access to the appropriate databases. You'll give this user the same name for each database that the new login can access. The @PASSWORD parameter, which contains the password you want to associate with the new login, is required only when you're creating a new SQL Server­authenticated login.

At callout A in Web Listing 1, the stored procedure declares the local variables it needs and creates a temporary table, #TMP_LOGIN_RIGHTS, to hold all the T-SQL commands to add the new login. The stored procedure populates this table and uses it in the following sections of code.

The code at callout B validates the input parameters and builds the command to add the new login to the server. The first validation test determines whether the old login exists on the server; if not, the code raises an error and the stored procedure terminates. The @NEWLOGIN parameter at callout B determines whether the new login is already defined on the server. If the new login already exists, the code raises an error and the stored procedure terminates. This section also determines whether the new login is a domain login or a SQL Server­authenticated login by looking for a backslash () in the @NEWLOGIN parameter. If @NEWLOGIN contains a backslash, the stored procedure assumes you're adding a domain account, builds the appropriate sp_grantlogin statement, and inserts the statement into the #TMP_LOGIN_RIGHTS table for use later. If the new login is SQL Server­authenticated, callout B's code tests to validate that an @PASSWORD value was passed as a parameter to the stored procedure. If no password is in the stored-procedure call, the stored procedure raises an error and terminates. After the stored procedure successfully performs the password test, it inserts an sp_addlogin statement into the #TMP_LOGIN_RIGHTS table that, when it executes, will add the @NEWLOGIN SQL Server­authenticated login to the server.

Next, the code at callout C grants @NEWLOGIN access to each database that @OLDLOGIN can access. To do this, the code declares a cursor, ALLDATABASES, which contains a list of all databases. The code also creates a temporary table called #TMPUSERS to hold information about existing database users. For each database that the cursor returns, the sp_helpuser system stored procedure populates the #TMPUSERS table with information for each database user that's defined in the current database. If the old login has access to the current database, the code determines whether the new user is already a database user. If the new user is already in the current database, the code generates an error message and aborts the stored procedure. Then, you can select a different username to associate with the new login. If the new user isn't in the current database, the code populates the #TMP_LOGIN_RIGHTS table with an sp_grantdbaccess command that grants the new login access to the current database. This process continues until the new login has access to all the databases it needs.

The existing login might have access to many roles in a database or to no roles at all. The code at callout D processes each database in turn, using the cursor (ALLDATABASES) and temporary table (#TMPUSERS) that the code at callout C defined. For each database it processes, the stored procedure determines whether the old login exists in the database and is a member of any database roles. If the old login is a member of a role, the stored procedure builds an sp_addrolemember command to insert into the #TMP_LOGIN_RIGHTS table. The sp_addrolemember command gives the new login membership to each database role that the old login was a member of.

In addition to database roles, the old login might have access to server roles. The code at callout E determines which server roles the new login should have access to. To accomplish this, callout E's code creates a temporary table, #TMPSRVROLES, and populates it with a list of members for each server role by using the sp_helpsrvrolemember system stored procedure. If the old login is a member of one or more server roles, the code uses the sp_addsrvrolemember system stored procedure to build a command to grant the new login access to each server role that the old login is a member of. The code inserts these generated commands into the #TMP_LOGIN_RIGHTS temporary table.

The code at callout F determines which object-, column-, or statement-level permissions are granted to the old login and builds the necessary commands to grant the same access to the new login. Callout F's code first creates a temporary table called #TMPPROTECT that holds the login's object-, column-, and statement-level permissions for a given database. The code processes through all the databases on your system one database at a time to determine whether the old login has permissions to access them; if it does, the code sets the variable @OLDUSER to the name of the database user associated with @OLDLOGIN. If the code sets @OLDUSER, it uses the sp_helprotect system stored procedure to obtain all object-, column-, and statement-level permissions for the current database. The code inserts the permissions into the #TMPPROTECT temporary table. If the database user @OLDUSER that's associated with @OLDLOGIN has object-, column-, or statement-level permissions, the code generates object-, column-, and statement-level GRANT permissions for @NEWUSER. The code also generates the database user that will be associated with the new login. Callout F's code generates different types of GRANT statements depending on the types of object-, column-, or statement-level permissions associated with @OLDUSER. The GRANT statements take into account whether @OLDUSER has the WITH GRANT OPTION for a particular object, and if so, the code includes the WITH GRANT OPTION in the generated GRANT command. The WITH GRANT OPTION on a database object lets the associated database user grant permissions on that object to another database user.

The code at callout G displays in Query Analyzer's Results pane each command that the previous six sections generated. The code builds a cursor containing each record in the #TMP_LOGIN_RIGHTS table. The code then processes this cursor one record at a time. The code uses a PRINT statement to output each record. The code can then use these printed T-SQL statements to define the new login and grant to the user the same server and database permissions associated with the old login.

Calling and Using the Stored Procedure

Now that you understand how the USP_GEN_USER_RIGHTS_BASED_ON_EXIST-ING_LOGIN stored procedure works, let's look at how to execute this stored procedure and use the T-SQL statements it generates to add a new login. For this example, say you want to add a SQL Server­authenticated login named NEWUSER. The new login will be based on an existing SQL Server login named EXISTINGUSER that has the following security profile:

Given the above information, you'd call USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN by using the following syntax in Query Analyzer:

EXEC DBO.USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN          @OLDLOGIN='EXISTINGUSER',          @NEWLOGIN='NEWUSER',          @NEWUSER='Joe the NewUser',          @PASSWORD='#NEWUSER964'

The @OLDLOGIN parameter identifies the existing login that you want to use as a model for the new login's security profile. Because the @OLDLOGIN parameter doesn't contain a backslash, you know that the old login represents a SQL Server­authenticated login. If you wanted to base your new login on the permissions of an NT user's existing login, you'd specify the @OLDLOGIN parameter by using the syntax .

The next parameter, @NEWLOGIN, specifies the name for the new login you plan to create. Again, because no domain is specified for @NEWUSER, you know that this login is going to set up a SQL Server­authenticated login. The @NEWUSER parameter identifies the username that will be defined in each database that the new login has access to. In this case, you want the database username to be Joe the NewUser. Because you're defining a SQL Server­authenticated user, you need to supply the password you want for the login. Best practices stipulate that you should have passwords for all SQL Server­authenticated logins, so the USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN stored procedure doesn't let you generate code to build a new SQL Server­authenticated login without specifying a password. After the stored procedure is finished executing, it generates the T-SQL output that Figure 1shows. Query Analyzer displays this code in its Results or Message pane or in a file, depending on the Results setting you specified for Query Analyzer.

To create the new login, highlight the generated EXECUTE and GRANT statements in the Query Analyzer Results pane, then copy the statements and paste them into an empty Query Analyzer window. After you've pasted the generated T-SQL code into Query Analyzer, simply execute the code to add the new login. Using this cut, paste, and execute method lets you review the T-SQL code before you execute it, so you can modify or remove some of the generated code if necessary. For example, you might want to remove a few permissions if the new login doesn't need exactly the same permissions as the existing login.

In addition to adding new logins, this stored procedure can help you audit permissions for an existing login. Simply call USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN with an existing login, but invent a dummy new login. This use of the stored procedure can help you determine which permissions an existing login has or why a login has or doesn't have rights to process commands against various databases and objects.

If you're going through the painstaking process of manually analyzing permissions and creating new logins with the same SQL Server permissions as an existing login, you might consider implementing a stored procedure similar to the one I describe in this article. DBAs are too busy to waste valuable time researching existing permissions, so anything you can do to streamline login administration is worth implementing.