Professional SQL Server™ 2005 Programming

22.3. User Rights

The simplest definition of what a user right is would be something like, "what a user can and can't do." In this case, the simple definition is a pretty good one.

User rights fall into three categories:

• The right to login

• The right to access a specific database

• The right to perform specific actions on particular objects within that database

Since we've already looked at creating logins, we'll focus here on the specific rights that a login can have.

22.3.1. Granting Access to a Specific Database

The first thing that you need to do if you want a user to have access to a database is to grant the user the right to access that database. This can be done in Management Studio by adding the user to the Users member of the Databases node of your server. To add a user using T-SQL, you need to use CREATE USER or the legacy stored procedure sp_grantdbaccess.

NOTE

Note that as you CREATE a user in the database, those permissions are actually stored in the database and mapped to the server's identifier for that user. As you restore a database, you may have to remap user rights to the server identifiers where you restored the database.

22.3.1.1. CREATE USER

The CREATE USER command adds a new user to the database. That user can be sourced from an existing login, certificate, or asymmetric key, or can be local to just the current database. The syntaxlooks like this:

CREATE USER <user name>
    [ { { FOR | FROM }
      {
         LOGIN <login name>
         | CERTIFICATE <certificate name>

| ASYMMETRIC ...