Managing Multiple-User Access

In "Understanding Usernames," August 2002, and "From Usernames to Roles," September 2002, I discussed basic methods for controlling database access and I showed you some T-SQL code for retrieving information about users and their roles. Now, let's take a look at a special role and a special username. The public role and the guest username are useful for managing permissions for groups of users. Because they both refer to users who haven't been added explicitly by name to the database, people sometimes confuse public and guest. I hope this article will clarify the differences between the two and help you use them effectively.

Open to the Public

The special role public is one of the built-in database roles that automatically exist in every database and that I discussed in "From Usernames to Roles." However, the public role has some special properties that the other fixed database roles don't have. One special feature is that every user belongs to the public role automatically and can never be removed from it. The primary purpose of the public role is managing permissions. By using one command, you can grant everyone in a database permission to do things such as retrieve data from a particular table or execute a particular stored procedure. The T-SQL commands for these tasks would look like this:

GRANT SELECT ON MyTable TO PUBLICGRANT EXECUTE ON MyProcedure TO PUBLIC

You can also use the DENY command to prohibit access to objects in a database. When you use DENY with public, you're disallowing access to everyone (excluding members of the sysadmin fixed server role, as we'll see shortly). No DENY command exists in SQL Server releases earlier than SQL Server 7.0; instead, you revoke permissions. When you revoke permission from public, you revoke access for everyone, including the username dbo. In SQL Server 2000 and 7.0, when you grant or deny permissions to the username dbo, the command has no effect. You don't get an error message, but nothing is recorded or changed in any system table.

Note that even in SQL Server releases earlier than SQL Server 7.0, if a user has the username dbo because she is the systems administrator (sa), that user always has full permission for all actions in every database. If the dbo's login name is sa, SQL Server bypasses permission checking and automatically lets that user run any command. In SQL Server releases earlier than 7.0, you can affect the dbo by revoking permission from the public role only if the database has a non-administrator owner.

In SQL Server 2000 and 7.0, when you deny rights to public, you don't affect the username dbo. However, any permissions you grant or deny to public will affect any object owner who is neither the database owner nor a member of the sysadmin role. So if a user named sue creates a table, then denies permissions on that table to public, no one—not even sue—will be able to access that table.

Be My Guest

I discussed the special username dbo in "Understanding Usernames" and "From Usernames to Roles." Another special username you need to know about is the guest username. As I mentioned, guest is a general-purpose username, so people often confuse it with public. You can add the guest username to any database, but unlike other usernames, guest never maps to a specific login name. If a database has a guest username, that database has an open door: Anyone who can access the SQL Server can access a database that has a guest username. If a user doesn't have a specific username in the database (which you give the user by using sp_grantdbaccess or sp_adduser), that user has the username guest.

In a database, the guest username acts as any other username does. You can GRANT or DENY permissions to guest, and if someone who has the username guest creates a table, the owner of the table is guest. If anyone who doesn't have the username guest wants to access a table that guest owns, the user must prefix the owner name to the table name, as follows:

SELECT * FROM guest.ATable

Note that even if you create a login named guest, no relationship exists between that login and the guest username. Any time you add the username guest to a database, the username will always be the special-purpose username guest, which doesn't map to any login. Therefore, no login named guest can ever specifically be granted access to a database. I suggest that you avoid confusion by never creating a login called guest. If you want a general-purpose login name that many people can use, invent another name such as SQLGuest or Visitor.

Guest Permissions

I can think of few reasons for granting explicit permissions to the guest username, but that doesn't mean that no one ever does so. (Because the management of permissions and the mechanism of combining users into groups or roles changed completely in SQL Server 7.0, the details I provide here apply only to SQL Server 7.0 and later unless otherwise noted.)

In particular, the creation script for the Pubs database (instpubs.sql), which you can find in the install directory of your SQL Server installation, includes several statements that grant permissions to guest on all of the user tables created in the script. The Pubs creation script also includes statements that grant the guest user permissions on the Data Definition Language (DDL) statements CREATE TABLE, CREATE PROCEDURE, and CREATE VIEW. These statements in the instpubs.sql script mean that everybody except those users who have specific usernames can create objects and perform SELECT, INSERT, UPDATE, and DELETE operations on the tables in Pubs. I don't know why the script contains those GRANT statements, but I guess that the original author of the script wanted to grant permissions to everybody and either thought that nobody would ever get a specific username in pubs or didn't understand the way the guest username works.

I frequently edit the instpubs.sql script—especially when I install the Pubs database for student use—and change all the GRANT statements that reference guest so that they reference the public role instead. When I make this change, then create the Pubs database, a student can explicitly create users in Pubs, and those users will still have access to all objects and actions, just as anybody using the username guest does.

Denying permissions to the username guest is another matter; there are very good reasons for doing so, especially if you deny permissions to guest after you grant permissions to the public role. Whereas granting permissions to guest means that everybody except users who have a specific username (i.e., not guest) has those permissions, executing the following statements will give everybody except guest users permissions to create tables, views, and stored procedures.

GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO PUBLICDENY CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO GUEST

Whenever I try to troubleshoot permissions problems in a database, I determine what permissions the guest username has. Listing 1 shows a procedure called sp_permissions_granted_to_guest. Running this procedure tells me which permissions the guest username has in the database that I'm calling the procedure from. If I also want to see what permissions are specifically denied to the guest user, I can rename the procedure and remove both lines containing the clause

AND protecttype IN  (204,205)

The T-SQL statements GRANT and DENY provide a great deal of flexibility when you're assigning permissions to users who have specific usernames. In next month's T-SQL Admin, I'll look at basic uses of the GRANT and DENY statements.

How to Get Access

Users who can log in to SQL Server can access a particular database in several ways. Two ways depend on the security ID (SID) associated with a particular login. If you want to find a login name's SID value, you can use the system function suser_sid(). For example, to find the SID for the login name sue, execute the following statement:

SELECT suser_sid('sue')

Here's a summary of ways a user can access a database:

After you've established a user's access to a database and the username that the user will have, you need to look at the permissions you assign in the database. In the next T-SQL Admin column, I'll introduce commands that you use for working with permissions in SQL Server and tell you about the system tables that track permissions information.