Kalen Delaney
I have a user who logs in as Rickey, whose objects in database sales are owned by guest. Another user, who logs in as Melissa, has objects in database sales that are owned by dbo. Where do dbo and guest come from? How can I allow Rickey and Melissa to own objects?
In SQL Server, the only thing your login name allows you to do is just what the name implies: you can "log in." In order to access any of the databases on the SQL Server, you must be mapped to a user name within the database you want to access.
Login names are stored in the master database in the syslogins table. The name column stores the login name, and the suid column stores the server user id that’s associated with that login name. Each database has a table called sysusers, which contains the user names of all valid users in that database. Associated with each user name in sysusers is a suid value that maps each user back to an associated login name.
You can add users to a database in one of two ways. Use the Manage Logins screen in the SQL Enterprise Manager where, for any login name, you’re given a list of databases. For each database, you can select whether this user can access that database and, if so, what the associated user name in the database will be. If Enterprise Manager isn’t available, or if you prefer to create auditable, reusable T-SQL scripts, you can use the system stored procedure sp_adduser:
sp_adduser Thomas, Tom
With the stored procedure approach, you need to be using the database in which you want to add a new user. The above procedure assumes "Thomas" is an existing login name, and will allow someone logged in as Thomas to use the current database under the user name of Tom. A user name can be the same as the associated login name, or it can be different. For ease of management, most system administrators keep the login names and user names the same, but you need to be aware that they’re really two different things. All object ownershipÑbear in mind that SQL Server "objects" such as tables and views aren’t the same as ActiveX objects or Java appletsÑrelates to your user name in a database, and all permissions are granted to user names, not to login names.
Two special user names will never match a login name exactly. Every database has a user name "dbo," which means database owner. If Melissa is the login name of the owner of the database sales, her user name will be dbo. Any objects she creates will be owned by dbo and any permissions granted to her within the database must be granted to dbo.
Every database can also have a user name "guest," which is the only user name that doesn’t map directly to a specific login name. If you have a guest user in sysusers, you’ll see that its associated suid is -1, which doesn’t match any suid in syslogins. You must use the sp_adduser stored procedure to add a guest user name, because there’s no login name to select in the Manage Logins screen:
sp_adduser guest
Adding a guest user basically gives the database an "open door" policy. It means that anybody who can log in to SQL Server can use this database, even if they don’t have an explicit mapping to a user name in sysusers. Anyone without a mapping in sysusers will use the user name "guest" when accessing the database. Any objects they create will be owned by guest, and the only permissions they have will be those that have been granted to "guest" or to "public" (which means everyone).
Can two login names share the same database user name?
To answer this question, I’ll have to admit that I wasn’t 100 percent correct in my answer to the previous question. Earlier, I said that anyone who doesn’t have an explicit mapping to a user name in sysusers can access the database as guest, if a guest user exists. There is one exception, however, which I’ll describe next.
The sysusers table has a unique index on name, which means that each user name can occur only once and must be mapped to a single login id (suid). However, there also exists in each database a table called sysalternates, which allows additional login names to be aliased to an already existing user name. Only one login name is mapped to a user name in sysusers, but additional login names can share that mapping by being aliased to the same user name. Aliases are valid only in the database where they’re defined, because there’s a separate sysalternates table in each database.
To create an alias in Enterprise Manager, you can use the same Manage Logins screen for the login name you’re working with. Choose the database for which this login is to have access. You must check the first column after the name of the database to permit this login to have access to the database. Then, instead of entering a value in the third column for a (new) user name, you’d use the fourth column. You can’t type anything other than a value from the drop-down list, which shows the names of existing users. Choose the one to whom you want this login name to be aliased. For the T-SQL approach, you could execute this statement:
sp_addalias Rickey, Kyle
This would allow someone using the login name "Rickey" to access the current database with the user name "Kyle." For this command to work, the user name Kyle must already exist and be mapped to another login name.
The most common use of aliases is to allow multiple login names to function as the database owner of a database. If Melissa is the owner of the database, you could also give ownership privileges to the login "dan" by executing the following:
sp_addalias dan, 'dbo'
Because "dbo" is a reserved word, it must be in quotes.
To determine what user name you’re currently using, you can examine the value of the user_name( ) function:
select user_name( )
If Melissa is the owner of database sales, why can’t she access the data in tables owned by users in her database?
By default, only the owner of a table has access to the data in that table, and the owner must grant permission to select, insert, update, or delete data. Even though the database owner has to give the user permission to create a table in the first place, once the user creates the table it belongs to that user. Then user "dbo" must be granted permission like anyone else. The one exception is if the database owner is the system administrator, logging in as "sa." Anybody who uses the login sa has full access to all data in every database, and permissions aren’t even checked.
There is a way around this, though, as you might have guessed. A database owner can impersonate any user in her database by using the setuser command:
setuser 'Kyle'
The user name must be a valid user in the database, and also must be in quotes. From this point on the dbo is treated exactly as if she were "Kyle." Any objects she creates are owned by Kyle. When accessing Kyle’s objects, she has full permissions and doesn’t need to specify the owner name with the table name. This can be very useful during testing, when the database ownerÑas the developerÑwants to test the objects and permissions that have been granted, and make sure Kyle can do everything he’s supposed to, and can’t do anything he isn’t supposed to! When the database owner is through being Kyle, she can issue the setuser command by itself to resume her old identity:
setuser
If the setuser command is issued with the WITH NORESET option, however, the database owner won’t be able to return to her true identity during that session.
setuser 'Kyle' with noreset
If you’re using setuser with SQL Server version 4.2, or if you’re familiar with it from pre-SQL 6.0 versions, there are some differences you should be aware of. The WITH NORESET option is new in SQL Server 6.0. Also, as of SQL Server 6.0, a database owner doesn’t need to issue the setuser command to DROP an object owned by a user in the databaseÑonly to access the data.
Prior to SQL Server 6.0, the "sa" (who always uses the user name dbo in every database) could also issue the setuser command, but the effect would be limited. The sa could create tables owned by another user, but could not test that user’s permissions. Why? Because even though setuser had been executed, SQL Server still treated the sa like the sa, and no permissions were checked. As of SQL Server 6.0, if someone who’s logged in as sa issues the setuser command, all sa capabilities are lost until the sa identity is resumed.
Finally, if you decide to experiment with using the setuser command, you can always verify who SQL Server thinks you are by using the user_name( ) function mentioned earlier. s
Kalen Delaney started working with SQL Server in 1987 when she worked for Sybase in Tech Support. Since then, she’s done SQL Server training both for Sybase and for Microsoft. Delaney currently provides independent training and consulting from her Seattle-area consultancy, Solid Quality Learning Inc. (www.sqlinc.com). Kalen_Delaney@compuserve.com.
To find out more about SQL
Server Professional and Pinnacle Publishing,
visit their website at http://www.pinpub.com/sqlpro/
Note: This is not a
Microsoft Corporation website.
Microsoft is not responsible for its content.
This article is reproduced from the June 1997 issue of SQL Server Professional. Copyright 1997, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call (800)788-1900 or (206)251-1900.