Last month, I discussed SQL Server login security and how to permit users or groups to connect to the SQL Server. (Answers to last month's practice questions are in "Answers to January Practice Questions: Login Security.") This month, I look at the next two steps in the security process: letting users access a specific database and using object permissions to control what users can do within that database. The security-related topics in the Microsoft's online exam guide for exam 70-28, the SQL Server Administration exam, include planning the use and structure of SQL Server roles. Server roles include fixed server, fixed database, and user-defined database. (For a more detailed explanation of roles, see "Login Security," January 2000.)
After you've set up login and connection permissions, the next step in setting security is giving the users access to the appropriate databases. You can grant these permissions from the Enterprise Manager or with stored procedures. From the Enterprise Manager, you edit a login by right-clicking the login name and selecting Properties. (If you're adding a login, keep the Login Properties dialog box open.) Select the Database Access tab in the Login Properties dialog box, as Screen 1 shows. You'll see a list of system and user-defined databases to choose from; check the appropriate box for each database the user needs to access. Note that you can give the user a name inside the database that's different from their login name. However, that arrangement might confuse your users, so avoid changing the name.
Another way to give a user database access is to use this syntax:
sp_grantdbaccess 'login_name', 'user_name_in _database'
The second parameter is optional; if you omit it, the user name in the database is the same as the login name.
Database Roles and Object Permissions
The next step in setting SQL Server security is letting the user read or modify the data in the tables. You usually set these permissions through the object permissions assigned to various database roles. Object permissions determine who can (or can't) read (SELECT) or modify (INSERT, UPDATE, or DELETE) objects in the database, such as tables and views. These permissions also control who can run a stored procedure.
After you give the login account permission to access a database, the lower window in the dialog box shows a list of database roles, as Screen 1 shows. The Public role is already checked because everyone who can access the database is a member of Public, and you can't drop anyone from this role. You can add the login account to multiple roles if the user needs more permissions than one role allows. You can't modify the supplied database roles, but you can add user-defined roles. Table 1 lists the supplied database roles.
The Transact SQL (T-SQL) syntax for adding a user to a database role is
sp_addrolemember 'role', 'user_name'
Be careful to use the correct syntax. For database roles, you give the role name first, then the user name. For server roles, it's user name first, then role name. Inconsistencies such as this one are favorites of people who write exam questions.
You're on a Role
After you finish placing users in various database roles, close the Login Properties dialog box. Expand the Databases item in the Microsoft Management Console (MMC) hierarchy, and then expand the database you just gave the users access to. Select the Roles item under the database to open the listing of roles for this database, as Screen 2 shows. Right-clicking any role and selecting Properties brings up the Properties dialog box for that role. From there, you can add other users. Public is the only role here that you can assign permissions for.
Create a New Role
Usually you'll find a role that fits your needs. But sometimes no role or combination of roles does quite what you need. Then you can add a role. For example, maybe you're setting up a database for a corporate charity fund-raising event, and no NT group has the right mix of users from every department. Rather than have the NT administrator create a new NT group, which could then be placed in the predefined roles, you might choose to create another role, place the users in it, and assign the appropriate permissions to that role. Right-click Roles and select the option to add a new database role. After you exit from the dialog box, thereby creating the role, you can set permissions on this role. Then you can add permissions.
The T-SQL syntax for adding a role is
sp_addrole 'role_name' , 'owner'
where owner defaults to the current user name, but specify dbo as owner to ensure that dbo owns the role.
The User-Centric View
You can right-click on Users, which is above Roles in the database tree, to add another user to your database. Or you can right-click a user name and select Properties, which brings up the Database User Properties dialog box. If you click Permissions, you'll see that no permissions are visible, as Screen 3 shows. What happened to the user's permissions from the Public role? They still exist, but here you're looking at permissions assigned to the user as an individual, not as a member of a role. From the Database User Properties dialog box, you can check and modify the user's membership in the database roles by selecting a role and clicking Properties. Selecting the Public role again lets you verify permissions for that role.
Object Permissions on Tables
So far, we've looked at permissions from the perspective of what permissions a role or user has to access or modify database objects. But you can also look at permissions on those objects to see which users or user-defined roles can access or modify specific objects. If you open the Tables item under the database object in Enterprise Manager, you can right-click a table and select All Tasks, Manage Permissions. This selection brings up a dialog box showing a grid of users and permissions. You can assign and remove permissions through this interface.
Or you can use the SQL syntax to assign permissions:
GRANT SELECT | INSERT | UPDATE | DELETE ON object_name TO user_name
To remove those permissions, use:
REVOKE SELECT | INSERT | UPDATE | DELETE ON object_name FROM user_name
To deny permissions to a user or role, use the same syntax that you use for GRANT, replacing GRANT with DENY. REVOKE undoes both GRANT and DENY, returning the user to a neutral state. In other words, the user has no permissions on the object, but isn't denied permission. Such users can't read or modify the object using their own permissions, but they wouldn't be prevented from doing so if they are members of a role that has permission.
You can also assign permissions at the column level. In the GRANT statement, make the object name the table_name.column_name. You can't assign column-level permissions through the Enterprise Manager, however. Microsoft apparently discourages the use of column-level permissions. This permission type adds a level of complex detail that you can avoid by simply denying access to the tables, then building views and stored procedures to let the users see and modify only the columns they need to modify. If you have 50 columns in a table and want to deny access to two of them, checking permissions on a view is faster than having SQL Server check permissions on all 50 columns, even allowing for the extra work in applying the view to read the underlying data.
Permissions to create, drop, and alter objects in the database are statement permissions. In practice, you grant permission to create objects. Implicit in that permission is the permission to alter or drop any object. The db_ddladmin role has these statement permissions by default, as does the dbo. To grant statement permissions from the GUI, right-click the database, select Properties, then select the Permissions tab, as in Screen 4.
To grant these permissions with SQL code, use the syntax
GRANT statement TO user_name
For example, if you had a group called SQLDevelopers, you might use
GRANT CREATE TABLE to SQLDevelopers
Use the stored procedure sp_helprotect to see a list of permissions in the current database. I recommend displaying the results in a grid in the Query Analyzer.
Permissions must be set and maintained at several levels with SQL Server. This is not a trivial task, but the multiple levels of permission allow a granularity in the permissions, so you can give users precisely the level of database access they need, and no more. For more examples of how to set permissions, see the lab files that accompany this article. You can download these files and answers to this month's practice questions (which appear in the sidebar "Practice Questions: Security and Permissions").