From Usernames to Roles

In real-life administration, you don't use pieces of information in a vacuum—your understanding of how usernames work influences how you understand and use roles, and that combined knowledge lets you effectively manage your SQL Server users. In "Understanding Usernames," August 2002, InstantDoc ID 25762, I began discussing core concepts that are essential to effective user management. I outlined the relationship between a login name in the master database's sysxlogins table (master..sysxlogins) and a database username and explained why you need to be aware of the difference between them. I also explained the importance of the special username dbo. Now, let's look at how and when you need usernames to specify object ownership and examine some basics of roles and role membership.

One reason that understanding usernames is important is that usernames own objects such as tables and stored procedures; a login never owns an object. So the login sa can never own an object, but the username dbo can own hundreds or thousands of them. Object names in a SQL Server database don't have to be unique, but an object name-owner name combination does have to be unique. Different users can own objects that have the same name. In fact, you might think of the owner name as part of the object name. Microsoft recommends that you avoid ambiguity by always prefacing an object name with its owner name. So to select from table1, whose owner name is sue, you would use the statement

SELECT * FROM sue.table1

If you don't specify an owner name when you refer to an object, you might think that you could encounter some ambiguity. For example, if you try to execute the command

SELECT * FROM table1

which table1 will SQL Server access? In fact, SQL Server sees no ambiguity because SQL Server has a two-part default for determining an object's owner. First, SQL Server determines whether the user executing the command is the owner of a table or view called table1 and, if so, accesses that table. If the current user doesn't own such an object, SQL Server determines whether the user dbo owns an object with that name and accesses dbo's table1 object if one exists. If neither the current user nor dbo owns an object called table1, SQL Server returns the following error message:

Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'table1'.

The only way for someone other than sue to access an object that sue owns is to qualify the object with the owner name. In my 15 years of working with SQL Server, this method of determining the object owner hasn't changed. And the confusion that the resulting error message generates also hasn't changed. New administrators can look in Enterprise Manager or run sp_help and see the table called table1. Thus, they think the problem must have something to do with permissions because they know that table1 exists. But often these administrators don't look closely at the supplied information regarding owner name or they don't realize when they need to specify an owner name.

Because the username dbo is one of the default object owners, you don't have to specify the owner for objects that dbo owns. This feature makes typing ad hoc SQL queries convenient and makes accessing the objects easy, so I used to recommend that you create objects with the owner dbo whenever possible. Although you can find good reasons for making dbo the owner of all a database's objects, I no longer consider ease of access to be one of those reasons. Now, my advice is to always specify the owner name of every object you access so that you always remember that the owner name is really part of the object specification.

So, say a user has left your company, and you need to change the owner of an object that she owns. Changing the owner of an object other than a table or a view is one of the functions that Enterprise Manager doesn't support. In fact, before SQL Server 7.0, T-SQL didn't support this change of owner either. If you wanted to change the owner of an object, Microsoft recommended that you drop that object and possibly all the objects belonging to that user and recreate them with the new owner. This approach seemed like overkill, so many administrators took the unsupported approach of directly accessing the system tables and changing the column that keeps track of the object owner. SQL Server 2000 and 7.0 now provide the stored procedure sp_changeobjectowner, which makes these same system-table changes. You can use this procedure to change the owner of any database object, not just tables and views. If you want to see what this procedure does, you can use the stored procedure sp_helptext.

Roles

As you add users to your SQL Server environment, you'll naturally encounter more difficulty in managing how those users access data. To make managing multiple users easier, SQL Server provides roles. The most important reason for assigning roles to your users is that it makes assigning permissions much easier. Although I won't be talking about permissions in this article, I'll describe the use of roles as an extension of usernames.

An administrator assigns roles to users in a database, but Microsoft and SQL Server Books Online (BOL) describe roles as if they're something that a user "belongs to"—like a group. In fact, in SQL Server releases earlier than SQL Server 7.0, users belong to groups instead of roles, but users can belong to only one group. In SQL Server 7.0, Microsoft changed the term to role, and now users can belong to multiple roles. (Roles are similar to Windows groups.) Not only can a user belong to multiple roles, but SQL Server provides built-in roles at the server level and at the database level, and each built-in role has a set of predefined permissions. Microsoft calls these fixed roles. To get a list of the permissions for the built-in server roles, you can run the command sp_srvrolepermission, and to get a list of the permissions for the built-in database roles, you can run sp_dbfixedrolepermission. (I'll talk more about permissions in upcoming T-SQL Admin columns.)

Although it seems as though server roles and database roles should be similar, they have some important differences:

Even though you can use sp_helpuser to see what roles a database user belongs to, the command returns additional information that I usually don't want. So I copied code from that procedure to create a new procedure called sp_listdbroles, which Listing 1 shows. Note that I've left the error-checking code up to you. (Also note that I'm directly accessing the system tables with this code—an activity that Microsoft discourages. Always use caution when you employ unsupported techniques.) Only three tables are involved in this procedure. The main SELECT statement uses a three-way join because sysusers has one row for each database user and one row for each role, and we need to access sysusers to get the username and the role name. The code in Listing 1 accesses an intermediate table called sysmembers, which is a linking table that indicates which user ID (UID) values are associated with which roles.

No procedure returns the server roles that a login belongs to, but sp_helpsrvrolemember shows which logins belong to which roles. So, by employing a little careful copying and changing which piece of information is parameterized, I've created my own procedure called sp_listsrvroles, which Listing 2 shows.

Within a database, the fixed role db_owner has all the rights and privileges that the username dbo has. In "Understanding Usernames," I told you that someone who is aliased to the username dbo isn't as powerful as the true owner, whose login ID is stored in master..sysxlogins. Adding a user to the db_owner role also isn't the same as making that user the true database owner—that is, users in the db_owner role can't issue the DROP command for the database and they can't restore the database from a backup. Another difference is that users in the db_owner role retain their own usernames, so any objects they create will have the original usernames stored with the objects. If someone other than the object creator wants to access any such object, she can't rely on SQL Server's default two-part process to find the owner, as I mentioned earlier. (Users who are aliased to the dbo user have the username dbo, so all objects they create have the owner dbo.) If you're aware of the fact that objects created by non-dbo members of the db_owner role won't be owned by dbo, you can use a workaround.

Users in the db_owner role can specify an object owner when they create an object. So if sue is in the db_owner role, she could create a table starting with this code:

CREATE TABLE dbo.table2

Users who aren't in the db_owner role will get an error if they try to create a table that has the owner dbo. Tables that ordinary users create can never show up as dbo.tablex.

Understanding usernames and roles is basic to good user management, and each new piece of information you learn adds to your ability to manage users effectively. In next month's T-SQL Admin column, I'll tell you about a special username and a special role, and I'll introduce some commands that you can use to manage permissions in SQL Server.