SQL Server Database Users
Once a database login has been successfully performed by any of the methods we've described, the connection to SQL Server must switch to the context of a database. When this is done, the login is mapped to a user context within each database. Note that a user context is database-specific, meaning that a single login can be represented as a different database user in each database. For example, EXAIR\Richard might be represented as Richard in one database but as RichardW in another. Without a database user mapping for a login, the user will not be able to use a database. Adding a login does not actually grant access to any particular database, except those databases with a guest user, explained below.
Database users are stored in the sysusers system table in each database. A user is tracked within the database via either his or her SID—the same SID mentioned earlier for each SQL Server login—or a user ID (UID) value. There is one-to-one mapping between an SID and a UID within a database, but a UID is referenced in all database-level system tables for historic reasons.
To grant database access, use the sp_grantdbaccess system stored procedure or the SQL Enterprise Manager. You can grant access to a standard security login, a Windows NT/2000 group, or a Windows NT/2000 user. You can also revoke access with the sp_revokedbaccess system stored procedure. No Deny option exists for database users.
Now let's examine database access for each type of login.
Standard Security Users
We'll start with standard security users because they're the easiest to understand. The first parameter to the sp_grantdbaccess system stored procedure is the login name that you'd like to add to a database. For instance, to create a login named Richard, you would run this:
exec sp_grantdbaccess 'Richard' |
The SID in the database will map to the SID of the login Richard.
Windows Groups and Users
We started with SQL Server logins because they're easy. Windows logins aren't that much harder to understand, but there's one catch—there's not necessarily a mapping of a login directly to a database user.
Let's start with the easy case. If the user EXAIR\Michael has been granted login rights to SQL Server and you want to give him access to a particular database, you'd run this:
exec sp_grantdbaccess [EXAIR\Michael] |
There's a one-to-one mapping just as with standard security logins.
It gets interesting when you want to mix groups and users, which is probably the best way to work with security anyway. Let's say that you've granted login rights to EXAIR\Domain Users and you then want to grant just EXAIR\Michael access to the accounting database. You would run the same command:
exec sp_grantdbaccess [EXAIR\Michael] |
Moreover, the entry generated in the sysusers table in the database is identical to the first sp_grantdbaccess. However, there's no one-to-one mapping back to any particular entry in sysxlogins since only the group had the right to log in. Because SQL Server has Michael's access token, it'll allow him to log in because of his group membership. When he tries to switch to this database, it'll recognize his primary SID and allow him in.
The sysusers Table and Database AccessNot all entries in sysusers necessarily have database access. When you grant permissions, as examined below, you'll normally grant them to a database user. However, you can also grant them to a Windows user who doesn't necessarily have a database username. Say you've added EXAIR\Domain Users as both a login and a database user in the Accounting database. You now issue the following command:
SQL Server will grant the create table right to Michael's account. When it doesn't find an entry for the user EXAIR\Michael in the sysusers table, SQL Server finds the SID for EXAIR\Michael and adds an entry to the table. However, the HasDBAccess flag in the sysusers table will be set to 0 (that is, no access). This means that the entry in the sysusers table is not there to allow database access for a specific database user. It's for some other purpose—in this case, to track permissions. Whether Michael has access to the database is up to you—either via some group membership that Michael has that gives him access to the database or via his user account in the database if the administrator specifically grants database access to EXAIR\Michael later.
Furthermore, if at some later point you explicitly grant Michael access to the database (via sp_grantdbaccess), all that will happen behind the scenes is that the row in sysusers will have the HasDBAcess flag reset from 0 to 1.
The dbo User
There are two special users in each database (potentially). The first of these is the dbo user, which exists in every database. It's the one and only true database owner, and it's always mapped back to a valid SQL Server login, since a SQL Server login must create a database. Ownership of a database can be reassigned, and doing so assigns a different login to the dbo user.
The Guest User
The other special database user is the guest user. Guest does what you'd think it does: it allows a valid SQL Server login to use a database, even if it wouldn't otherwise get database access. There's always a guest user in the master database—so that logins have some place to go when they first get created—and in the tempdb database—because even simple help procedures use tempdb to build the answers to some requests. You can't delete guest in these databases. In every other database in SQL Server, you can add guest by running sp_grantdbaccess for guest.
NOTE
You'll find a row in sysusers for guest even if you haven't turned on the guest account for that database. The entry has the HasDBAccessflag set to zero, or no access. This is used to reserve the namespace for guest.
EAN: N/A
Pages: 138