Understanding Usernames

EDITOR'S NOTE: Send your T-SQL questions to SQL Server MVP Itzik Ben-Gan at [email protected].

No matter how many servers or users you manage, a major part of your job as a SQL Server administrator involves assigning and managing users and permissions within your databases. Part of that job is managing usernames and the roles that users belong to. The job also includes aliasing database users. An alias is an alternative username that lets users work with the same rights as another user. Although Microsoft treats aliases as a legacy feature (SQL Server 2000 supports aliases for backward compatibility only), certain tasks such as granting dbo-equivalent rights are easier to accomplish when you use aliases. And many existing systems use aliases, so you need to know about the feature so that you can maintain such systems. Let's look at some commands that you can use to manage usernames in a database and see how you might use aliasing. Note that some of these commands have no GUI equivalents, so you must use T-SQL. In upcoming T-SQL Admin columns, I'll address role membership and permissions.

Logins vs. Usernames

User access to SQL Server begins with a login name, which lets users into the SQL Server system but doesn't allow access to specific databases. Creating login names for SQL Server is beyond the scope of this article, so I'll start by assuming your OS users have access to SQL Server either because of their membership in an OS group that uses Windows authentication or because the system uses SQL Server authentication and users have SQL Server­specific login names and passwords. Regardless of whether a login name is determined by Windows authentication or SQL Server authentication, that login only lets the user in SQL Server's front door. To get into a database, users must have a username in that database. Then, to access any objects in the database, users must have a login name that's mapped to the username in the database, and that username must be granted appropriate rights in the database. (For more information about setting an authentication mode and planning your system security implementation, see Morris Lewis's SQL Server Magazine articles "Security in SQL Server 7.0," December 1999 and "Creating a Manageable Security Plan," October 2000.)

One of the most important concepts for a DBA to understand is the difference between login names and database usernames. By default, when you use supplied tools such as Enterprise Manager or a T-SQL stored procedure for granting access to a database, you create a username that's the same (or almost the same) as the corresponding login name—but the names don't necessarily have to match.

To grant access to a database, you can use the supplied stored procedure sp_grantdbaccess. You must run sp_grantdbaccess in the database that you're giving a login name access to. Sp_grantdbaccess takes two parameters, but because the second parameter is optional, many administrators are aware of only the first one. The first sp_grantdbaccess parameter is the login name that you want to give database access to, and the second parameter is the name that the login will use in the database—that is, the database username for the given login. If you omit the second parameter, the login name and username will be the same. Thus, usernames and login names often end up being the same, and administrators don't realize that a crucial difference exists between the two names.

The following EXEC statements are examples of how you might use sp_grantdbaccess with different usernames and login names:

USE mydbEXEC sp_grantdbaccess [Domain1sam]EXEC sp_grantdbaccess [Domain2Peregrin], pippinEXEC sp_grantdbaccess frodo

The first statement grants a Windows-authenticated login access to the database mydb. Because the statement doesn't specify a second parameter, the username in the database is the same as the login name: Domain1sam. In the second statement, the Windows-authenticated login Domain2Peregrin gets access to the mydb database, and the database username is set to pippin. And the third statement grants database access to a SQL Server­authenticated login under a username that's identical to the login name, frodo.

When you map a login to a database user, SQL Server stores the database username in the sysusers table of the database you're granting access to. You can allow the login Domain2Peregrin access to the Pubs database by using the username Domain2Peregrin, allow it into Northwind by using the username Peregrin, and deny it access to the Inventory database—all at once. This set of database access rights would mean that a row in the Pubs database's sysusers table (pubs..sysusers) would have the name Domain2Peregrin, which would map to the row for Domain2Peregrin in master..sysxlogins. A row in the Northwind..sysusers table would have the name Peregrin, which also would map to the sysxlogins row for Domain2Peregrin. And no row in the Inventory..sysusers table would map to the sysxlogins row for Domain2Peregrin.

Owner Operated

Another important row in each database's sysusers table is the row for the username dbo. The row for dbo maps to the row in master..sysxlogins for the server login that belongs to the database owner. Dbo never has a login name—it's only a database username. However, it's probably the most important database username. Often, the database user dbo maps to the master..sysxlogins row for sa or for a login that's a member of the sysadmin (system administrators) fixed server role, but this mapping isn't required. A login can become a database owner in two ways. The first way is if an administrator grants the login permission to create a database, and someone using that login creates a database. For example, if an administrator grants the login merry permission to create a database, and someone using that login creates a database, merry is the database owner. The row for username dbo in sysusers in the newly created database would map to the master..sysxlogins row for merry. The other way merry could become a database owner is if an administrator uses the login merry as a parameter to the stored procedure sp_changedbowner while accessing an existing database. For example, the statement

EXEC sp_changedbowner merry

modifies the sysusers table in the current database so that the row for the user dbo maps to the master..sysxlogins row for merry. However, if another row in sysusers already maps to merry, you get the following error message:

Server: Msg 15110, Level 16, State 1,Procedure sp_changedbowner, Line 46The proposed new database owner is already a user in the database.

Note that sp_changedbowner is a T-SQL operation that has no equivalent in Enterprise Manager.

Although it's natural to think of dbo as meaning database owner, you might want to think of dbo simply as a special username. I think of a database's owner as the login name listed in the master..sysdatabases table. So in my mind, each database has only one owner. For example, if the login Domain2Peregrin created a database, Domain2Peregrin would be the database owner. This owner has the username dbo in a database that he or she owns, but other logins can also have the username dbo. By default, a user who has the username dbo can perform any action within that database. Permission to perform some actions—for example, executing stored procedures—can be granted to other database users. However, the user dbo has many rights that can't be transferred to another user, including adding new members to fixed database roles and running many of the Database Consistency Checker (DBCC) commands.

Using an Alias

What if you want more than one person to have all the rights of the user dbo so that SQL Server will treat multiple users as the user dbo? When multiple users connect by using the same SQL Server login, they all have the same username in every database. So, let's say that Sue is the owner of multiple databases, and you want Joe to have full dbo user rights in one of Sue's databases but not all of them. In SQL Server 2000 and 7.0, the closest you can come to these overlapping permissions is to create an alias for the user dbo. As I mentioned earlier, Microsoft recommends that you avoid using aliases and use database roles instead because SQL Server 2000 supports aliases only for backward compatibility. But the two methods aren't exactly equal—I'll tell you why in the next T-SQL Admin column—and neither method truly gives a user all the privileges of the real database owner. Microsoft doesn't support using Enterprise Manager for creating aliases, so you have to use the stored procedure sp_addalias. To tell SQL Server to treat Joe as the user dbo in the mydb database that Sue owns, you would use the following code:

USE mydbEXEC sp_addalias joe, dbo

Keep in mind that the first parameter of this procedure is a SQL Server login name, not an existing username in the database. In fact, if the username joe were already listed in the sysusers table in mydb, the above statement would produce an error. The command tells SQL Server to map to the database username dbo a login name that doesn't already have access to mydb. When the preceding command executes, SQL Server adds a row to the sysusers table in mydb for the username joe, so the username joe can't already exist. Joe's row will have its own unique user id (UID) value but will have a value of 1 in the column that the alias is in. Joe will have an existing UID value in the altuid column—in this case, the same UID as the dbo user, which is always 1. So now, although Joe is not the true database owner, within the database mydb, he has all the rights and privileges of the user dbo. His username will show as dbo when he executes the command

SELECT user_name()

and all objects he creates will have the owner dbo.

However, Joe still doesn't have all the rights and privileges that Sue has. In particular, he can't issue the DROP command for the database Sue owns, and he can't restore the database from a backup.

Another fact to remember about the dbo username is that whenever an administrator uses a database, his or her username is dbo, regardless of the entries in the sysusers table. Such users can perform actions such as dropping and restoring a database, not because they have the username dbo but because they're SQL Server administrators, and SQL Server doesn't restrict the activities an administrator can perform.

When you understand how usernames work, you can begin thinking about the roles each user should belong to. In the next T-SQL Admin column, I'll finish discussing database usernames and introduce database roles. I'll also show you the T-SQL code for determining what roles a user belongs to.