To grant privileges to groups that don't have explicit login entries in the Sysxlogins system table, you must leave Enterprise Manager because it lets you choose only from a list of existing logins, not the entire list of groups in the domain. To access all the groups, open Query Analyzer, and use the system stored procedures sp_addsrvrolemember and sp_addrolemember to grant privileges. (For details about these stored procedures, see SQL Server Books Online—BOL.)
For the server operator groups, you use sp_addsrvrolemember to add each login to the appropriate server role. (For more information about server roles, see Michael D. Reilly's Certifiably SQL columns "Security and Permissions," January 10, 2000, InstantDoc ID 7858—online only—and "Login Security," January 2000.) The SQL Server Administrators login becomes a member of the Sysadmins role, SQL Server DB Creators becomes a member of the Dbcreator role, and SQL Server Security Operators becomes a member of the Securityadmin role. Remember that the second parameter of sp_addsrvrolemember requires the complete path for the account. For example, JoeS in the BigCo domain would be bigco\joes. (Note that if you want to use a local account, the path would be server_name\joes.)
To create users that exist in all new databases, change to the Model database. SQL Server automatically copies anything you do in Model into new databases, which can make your job easier. If you use Model correctly, you won't need to customize every new database after its creation. Using sp_addrolemember, add SQL Server Security Operators to db_securityadmin, and add SQL Server Developers to the db_owner role.
Notice that you still haven't granted any group or account access to the database. In fact, you can't grant database access through Enterprise Manager because the user interface lets you grant database access only to valid login accounts. SQL Server doesn't require an NT account to have access to the database before you can make the account a member of a database role or assign object permissions, but Enterprise Manager does. You can assign permissions to any NT account in the domain without granting that account database access as long as you use sp_addrolemember and not Enterprise Manager.
You're now finished with Model unless your users fall into broad categories across the organization as a whole. In that case, you can perform the following tasks in Model instead of the application-specific database.
Permitting Database Access
Inside a database, you can do something differently from what you've done so far with login authentication: You can assign permissions to roles instead of assigning them directly to a global group. This capability lets you add SQL Server authenticated logins to your security plan with little additional effort. Even if you never expect to use SQL Server login accounts, I still recommend assigning permissions to roles so that you're prepared in case your requirements change in the future.
After you create a database, use the stored procedure sp_grantdbaccess to grant the DB_Name Users group access to it. Note that no corresponding sp_denydbaccess stored procedure exists, so you can't deny access to a database in the same way you can deny access to the server. If you need to deny database access, create another global group named DB_Name Denied Users, grant it access to the database, and make it a member of the db_denydatareader and db_denydatawriter roles. Be careful about how you assign statement permissions because these roles limit only access to objects, not to Data Definition Language (DDL) statements.
As with logins, SQL Server lets a user access a database if any SID in the access token has an entry in the Sysusers system table. So, you can grant users access to a database through either their personal NT account SIDs or any NT group (or groups) of which they're members. To keep management simple, create one global group named DB_Name Users that has database access, and don't grant access to any other group. By doing so, you can add and remove database users simply by adding or removing membership in the global group.
Assigning Permissions
The last step in setting up your security plan is creating user-defined database roles and assigning permissions. The easiest way to handle this step is to create roles with names that match the names of the global groups. Using the accounting example, you would create roles named Accounting Data Entry Operators, Accounting Data Entry Managers, and so on. You could shorten the names because roles in the Accounting database will probably relate to accounting tasks. However, if the role names match the global group names, you will have less confusion and can more easily identify which groups belong in a role.
After you create the roles, you can assign permissions. For this step, just use the standard GRANT, REVOKE, and DENY statements. But be careful with the DENY permission because it takes precedence over all other permissions. SQL Server denies users access to an object if they are members of any role or group with the DENY permission. Also, if you're upgrading from SQL Server 6.5, remember that SQL Server 7.0 and SQL Server 6.5 assign and evaluate permissions completely differently.
Now, you can add any SQL Server authenticated logins you have. One of the most valuable features of user-defined database roles is that they can contain SQL Server logins as well as NT global groups, local groups, and individual accounts. User-defined roles are a universal container for all types of logins, which is the main reason to use them instead of assigning permissions directly to the global groups.
Notice that I'm suggesting that you use only two built-in database roles (db_securityadmin and db_owner). My reason is that built-in roles generally apply to the database as a whole, not to individual objects. For example, db_datareader grants SELECT permission on every object in the database. You could use db_datareader, then selectively deny SELECT permissions to individual users or groups, but that method creates an opportunity for you to forget to set the permissions for some users or objects. An easier, more straightforward, and less error-prone method is to create a user-defined role for a specific set of users and assign the permissions that those users will need only for the objects they need to access.
Simply Secure
SQL Server authenticated logins are simple to implement and easier than NT authenticated logins to code into programs, but they aren't easy to manage when the number of users exceeds 25, when you have multiple servers, when each user has access to multiple databases, or when the database has multiple administrators. Even in small installations in which the database administrator has other responsibilities, simple plans alleviate the problems of remembering each user's permissions and how he or she got them—a problem made more complex because SQL Server doesn't have a tool that shows users' effective permissions. The best recommendation, therefore, is to use NT authenticated logins and to manage database access with a carefully selected set of global groups and database roles.
Here are rules of thumb for keeping things simple:
- Users get server access through the SQL Server Users group and database access through the DB_Name Users group.
- Users get permissions by being members of a global group that is a member of a role that has the permissions in the database.
- Users who need multiple sets of permissions can be members of multiple global groups.
With proper planning, you can handle all maintenance of access rights and permissions on the domain controller so that changes are reflected in all the servers you manage. You might have some different variations, but the basic steps I've outlined will help you to create a manageable security plan.