Users

When a login is granted access to a database, a user is created in the database with the same name as the login by default. The user is given specific rights to database roles or to granular objects inside the database, such as EXECUTE rights on a stored procedure. One method to create a user shown earlier is through the Login Properties dialog box. In that dialog box, you can go to the User Mappings page to check which databases you wish to add login in and which roles you wish to assign to the user.

A person assigned the db_owner database role would not have access to the Login Properties dialog box, though, unless he or she were a member of a specific server role, like sysadmin. The alternative way to do this, which most DBAs prefer, is to go to the individual database, right-click the Users folder under Security, and select New User. The window shown in Figure 9-6 appears.

First, select the ellipsis button next to the Login Name option. Select the login name that you wish to add to the database. Then type the user name that the login will be mapped to. The Default Schema field specifies the schema that the user's objects will be created in and selected from. If you don't specify for this optional setting, the objects will be created with the user's name, like UserName.TableName. If the user owns a given schema, that will show in the "Schemas Owned By User" grid. (Schemas will be covered much more in a later section of this chapter.) Last in this screen, you must specify which database roles you wish the user to be a member of. (Database roles are discussed in detail in the login section of this chapter.) If you've created user-defined database roles, they'll show up in this list as well.

Granular Permissions

Let's take a look at a quick common example. If you were to grant a user db_datareader and db_datawriter rights, he would be able to read and write to any table in the database. He would not be able to execute stored procedures, however. Generally speaking, you will want to have all your data access through stored procedures. There are numerous reasons for that, which are listed in Chapters 6 and 7, but one of them is security. If you can encapsulate access through stored procedures, you can add auditing and guarantee that all access to your data will be done a certain way.

If the user had the rights to the database as shown in Figure 9-7 with no EXECUTE rights granted to the stored procedure, the user would receive an error like this if he tried to access any stored procedure:

 Msg 229, Level 14, State 5, Procedure SelectSproc, Line 1 EXECUTE permission denied on object 'SelectSproc', database 'TicketSalesDB', schema 'dbo'. 

Figure 9-7

If the user had stored procedure access, he would likely not need access to the underlying tables. There are exceptions to that rule in the case of dynamic queries. There are ways around that, though, by using context switching. To allow your users access to stored procedures, you need either to grant them access to each stored procedure or grant them overarching rights to the database, but there is no database role for EXECUTE rights. The disadvantage to granting the user access to each stored procedure is that it becomes a bit of a management headache. The disadvantage to granting overarching rights to every stored procedure is that you may have stored procedures you don't wish the user to access.

For the purpose of this example, let's look at both situations. Typically, in an enterprise application environment, a DBA would be granular and would grant a user access to each stored procedure individually. This is usually done when the DDL is originally created.

For either example, you go would go to the Securables tab of the Database User dialog box and click Add. Even if you have already granted granular permissions, they will not display in the permission grid until you click Add. For the overarching permission, you would want to show the database securables. For individual rights to each stored procedure, you would want to display each stored procedure.

Both of these scenarios can be seen in Figure 9-7. By selecting the database securable, you can select Execute from the Grant column, and now the user can execute any stored procedure in the database. If you select an individual stored procedure, you can select Execute to grant rights to execute the single stored procedure. If you check the securable from the With Grant column, the user will be able not only to execute the stored procedure but also to give others rights to do so as well.

On a stored procedure or almost any other database object, there are other securable actions that you may wish to enable. If you wish your developers to be able to see the schema in production but not alter the schema, you could grant them the View Definition permission and deny them Alter rights. The Control permission would enable a person to perform any action on the object, much like a db_owner does but at a granular level.

Roles

We have already discussed system database roles, but you can also create user-defined roles inside of your database. User-defined roles are essentially groups inside SQL Server to group your users together. If you have gone through the trouble of granting permissions for the users to be able to access only a select few stored procedures, you can use roles to ensure that every user thereafter would inherit those rights.

To do this, you would essentially create the database role and then grant the rights to the role just as you do a user. When you grant new login rights to the database, you would then assign them to the role with no other permissions. The user then inherits all rights given to him via the role.

Creating a role is simple in Management Studio. Right-click Database Roles under the database, click SecurityRoles, and click select New Role. Name the role and then specify who owns the role. You can then go through the same screens that we discussed earlier in the Users section to specify what the role has permissions to do.

In T-SQL, you can use the CREATE ROLE DDL syntax to create a role. Simply name the role and then specify the owner of the role with the AUTHORIZATION keyword, like this:

 CREATE ROLE [WebUsers] AUTHORIZATION [bknight] 

Creating Users with T-SQL

To create a user through T-SQL, you can use the CREATE USER DDL. For example, if you wish to grant the login "LoginName" access to the database as a user named "LoginName," you could use the following syntax. The brackets around the login and user name allow the login to have spaces.

 CREATE USER [LoginName] FOR LOGIN [LoginName] 

With the user now created, you will want to add the user to various roles. The most common roles to add a user to are db_datareader and db_datawriter, as you'll see. To do this through T-SQL, you would use the sp_addrolemember stored procedure. You would then pass in the role you wish to add the user to, followed by the user name. We prefixed each parameter with an N, since each input parameter is a Unicode value. You can use sp_addrolemember to add the user to user-defined roles like the WebUsers role you created earlier in the previous section.

 EXEC sp_addrolemember N'db_datawriter', N'LoginName' EXEC sp_addrolemember N'db_datareader', N'LoginName' EXEC sp_addrolemember N'WebUsers', N'LoginName' 

Last, you can grant the user rights to various securables by using the GRANT statement. The syntax will vary based on what you're trying to grant access to. The following three examples show how to grant the user access to create schemas and tables and the ability to execute the SprocName stored procedure.

 GRANT CREATE SCHEMA TO [LoginName] GRANT CREATE TABLE TO [LoginName] GRANT EXECUTE ON SprocName to [LoginName]