As the main article mentions, having a login name for SQL Server only lets you in the system's front door. To access a database, you need a username in that database, and to access objects in the database, your username must have appropriate rights. Also, a username in a database marks object ownership; if a database user has permission to create an object and does so, that user is the object's owner. For example, if database user sue has permission to create a table and she does so, sue owns the table, and anyone other than sue who wants to access sue’s table must specify the object owner’s name (e.g., SELECT * FROM sue.NewTable).
As I stressed in the main article, you need to understand usernames so that you can avoid username glitches when you perform a restore operation. Let’s take a look at how administrators grant users access to databases, how SQL Server stores usernames in each database’s sysusers table, and how to use the special username dbo.
To grant access to a database, an administrator can use the stored procedure sp_grantdbaccess (or its Enterprise Manager equivalent). You must run this procedure in the database that you want to give 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 parameter is the login name that you want to give database access to, and the second 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. Because many people don’t know about the second parameter, usernames and login names end up being the same, and many administrators don’t realize that the names are different. But the difference is crucial. Here are three example statements that grant access to mydb:
USE mydb EXEC sp_grantdbaccess \[DBAdminDomain\joe_blow\] EXEC sp_grantdbaccess \[MyPrivateMachine\sue_hoo\], sue EXEC sp_grantdbaccess andre
In the first statement, a Windows-authenticated login was granted access to the database mydb. Because no second parameter is specified, the username in the database will be the same as the login name: DBAdminDomain\joe_blow. In the second statement, the Windows-authenticated login MyPrivateMachine\sue_hoo is granted access to the mydb database, and the username in the database will be sue. In the third statement, a SQL Server–authenticated login is allowed into the database under a username that's identical to the login name, andre.
The default doesn’t always create a Windows-authenticated username that’s exactly the same as the login name. If you open the Security folder in Enterprise Manager, right-click a login name in the right pane, and select the Database Access tab to add new users to a database, the default database username strips away the domain prefix; so DBAdminDomain\joe_blow would have the username joe_blow in the database. A SQL Server developer at Microsoft told me that this behavior is incorrect for Enterprise Manager, but he wouldn’t go so far as to admit that it’s a bug. Microsoft might change it in the next release of SQL Server, code-named Yukon. If the domain prefix is eliminated, you can override the default and explicitly type in the domain prefix. Also, in Enterprise Manager, if you select the folder for a particular database and choose the Users folder within that database, any Windows-authenticated logins you add as database users will keep their domain prefixes. Although it might be easier to work with a username such as joe_blow than a username such as DBAdminDomain\joe_blow, which must always use a delimiter in any T-SQL statements, it’ll be nice if Microsoft does change the incorrect behavior of Enterprise Manager so that the defaults in Enterprise Manger and T-SQL are the same. And if you have a mixed environment of both Windows- and SQL Server–authenticated logins, keeping the domain prefix in your usernames will let you easily distinguish which database users are associated with which type of login.
Understanding sysusers
SQL Server stores usernames in a database's sysusers table. You can let the login DBAdminDomain\joe_blow access the Pubs database by specifying the username DBAdminDomain\joe_blow, give it access to the Northwind database under the username joeB, and deny it access to the Inventory database. This setup would mean that a row in the Pubs database's sysusers table (pubs..sysusers) would contain the name DBAdminDomain\joe_blow, which maps to the sysxlogins row for DBAdminDomain\joe_blow. And a row in the Northwind..sysuysers table would contain the name joeB, which also maps to the sysxlogins row for DBAdminDomain\joe_blow. And no row in the Inventory..sysusers table would map to the sysxlogins row for joe.
The sysusers table uses the column sid to map to the sysxlogins table. If the login is a Windows-authenticated name, the value in the sid column is the security ID (SID) that the OS generated when the user was first created. If the login is a SQL Server–authenticated login, SQL Server will generate its own sid column value when a SQL Server administrator creates the new login name. When SQL Server executes the sp_helpuser stored procedure in a database, it can tell you which login name is associated with each user in a database by joining the sysusers and the sysxlogins tables together based on the common sid column. One exception is when no matching value exists in the sysxlogins table. If you use Windows authentication to grant SQL Server access to an OS group, you can grant an individual user of that group database access even though that user has no specific row in sysxlogins. For example, if I granted SQL Server login access to MyDomain\SQLUsers, and username sam is a member of the SQLUsers group, I can grant database access to sam, as the following statement shows:
EXEC sp_grantdbaccess \[MyDomain\sam\]
The sysusers table will have a row for MyDomain\sam that contains sam’s OS SID. No corresponding row in sysxlogins will contain that SID; only the row in sysxlogins for the SQLUsers group will exist. Every time user sam needs to access the database, SQL Server will have to verify sam’s continued membership in the OS SQLUsers group.
The dbo Username
You need to know about another row in each sysusers table: the row for the username dbo. The dbo row maps to the row in sysxlogins for the database owner’s server login. This row's value is frequently the login name sa or a login name that’s in the sysadmin fixed server role. Alternatively, the database owner might belong to an OS group such as BUILTIN\Administrators (which has a row in the sysxlogins table), in which case the individual database owner won’t map to a row in sysxlogins. But the owner of a database doesn’t have to be an administrator. You could make the login sue the owner of a database by granting sue permission to create a database and having her use that privilege or by using the stored procedure sp_changedbowner within a particular database to change the owner to sue. In addition, no matter who is mapped from sysusers to sysxlogins for the user dbo, anyone who logs into a SQL Server by using the login name sa or any login name that belongs to the sysadmin fixed server role will have the username dbo in any database they use. This privilege is part of the power of being an administrator. The sysusers table might indicate that dbo maps to the login sue, but every administrator will also have the username dbo, as Query Analyzer will verify. If you log in as sa or another administrator and use a database that a non-administrator owns, the following statement will confirm that, regardless of who the true owner of the database is, your name in the database is dbo:
SELECT user_name()
Because any administrator’s username is always dbo, whenever an administrator uses a database, any object the administrator creates has the owner dbo by default. Unlike a table that sue creates, if the user dbo owns a table (or other object), anyone can access that table without specifying the owner name. However, Microsoft recommends that you always consider the owner name to be part of the object name, and you should use the owner name whenever possible when referencing database objects.
Although you might usually think of dbo as meaning database owner, you might be able to better understand the difference between the username dbo and the database owner if you think of dbo as simply a special user name. The login sa is never a username—it’s only a login name. Thus, the statement
SELECT user_name()
will never return the value sa, and sa will never own any objects in a database because only users in a database can own objects.