Defining SQL Server Security Roles for Better Security Management

SQL Server comes with a long list of database, application and server roles which outline data selection and modification, disk management and permissions among other aspects. These roles are important in maintaining server security.

Many SQL Server databases have multiple users accessing and viewing data, giving the administrator extra concern regarding maintenance of security. A wise administrator should take complete advantage of security roles in SQL server, diving groups of users according to their database access needs and setting permissions in a way as to reduce security risks in the system.

This begins with deciding the users that need to view data of any kind stored in the database, and ensuring that users can only access the data they need to do their jobs. For instance, a payroll processing clerk might need access to salary information for all other employees in the organization, while a team manager would only need to view salaries of team members under his supervision. Individual employees in other departments wouldn’t need to know the salaries of any other employees.

The second step is determining which users should be granted permissions to edit/change the data. For instance, a person in sales/fulfillment may need to view clients’ addresses, but only designated trained data entry personnel or accounts executives should get rights to change those addresses.

Factoring in the needs of many users would be a daunting task in the absence of provisions of the Windows security model, where a single role definition can accommodate multiple user groups. The role feature defines the things that users of a database can and cannot do, so that multiple users can have a similar role. This article discusses the importance of role definition in relation to Windows groups, and how these roles can be used to grant or refuse access to many users simultaneously.

Benefits of SQL server role definition

Roles form part of the Windows tiered security model which include login security controlling connection to the server, database security controlling access to the database and database objects which control access to separate database objects and data stores.

A user may access the server by keying in their authorized username and password. Once connection to the server has been established, user account information is used to determine access to stored databases. Within the actual database accessed, the user is limited only to data that he/she can view and/or modify.

Efficient account management is the most significant benefit of roles in SQL server. Picture a group of 500 viewers who need to either view or modify some data. In Windows security model, all the administrator needs to do is to take a defined Windows group and assign an SQL server role to it, rather than modifying individual accounts. Windows groups are made of users who can access the Windows network, while SQL server roles are limited only to SQL server. It’s simply allowing valid Windows user groups to access data, objects and databases in SQL Server.

Types of roles

The database roles maintained by a server administrator are applicable for the entire server system, rather than any single database/file. There is a public role which establishes default permissions accessible to all users. Every new user that is added onto the SQL Server is automatically assigned to this role – you don’t need to set this permission.

Predefined database roles

Individual databases have their separate database roles also. You may want to create your own database roles, but SQL server comes with these predefined roles:

db_owner – for members that have full database access
db_accessadmin – for members who can control SQL Server sign ins and Windows groups
db_datareader – members can view all data but cannot edit
db_datawriter – members can add, edit or delete data within database tables
db_ddladmin – members are allowed to run dll (dynamic link library) statements
db_securityadmin – members can control permissions and edit/modify role memberships
db_backupoperator – members can create backups of the database
db_denydatareader – members that cannot view data stored in the database
db_denydatawriter – members that cannot add, edit or delete data in views or tables in database

Fixed roles

Fixed roles are applicable to the entire server, and some predefined roles are listed below:

SysAdmin – member can perform any action within the server
ServerAdmin – member can set and modify server configuration options
SetupAdmin – member can control SQL Server startup configurations and tasks as well as manage linked servers
SecurityAdmin – member can control server security settings
ProcessAdmin – member can kill running processes on SQL Server
DbCreator – member can create, modify, drop and/or restore databases
DiskAdmin – member can control disk files in SQL Server
BulkAdmin – member can execute the bulk insert server command

Illustration: Assigning roles

For this illustration, assume we’re using SQL Server Enterprise Manager to add a new Windows group on the Pubs database (default sample database which comes with the 2000 version) role. First, you will need to create login ID for members of the Guests group in Windows, allowing them access to the SQL Server. You can do this by:

Launching Enterprise Manager and expanding the security folder
Right-clicking on Logins item and selecting New Login from the submenu that rolls to open the New Login dialog box. By default, the Windows Authentication mode should be checked; select it if it isn’t checked in your system. Windows authentication mode in this case is recommended rather than the SQL Server mixed mode. This is because the latter comprises both Windows’ and SQL server’s security model.
Clicking on the button located on the right-hand-side of the Name control which displays SQL Server Login Properties dialog box. In our case, we’ll be using the Guests account. Select the Add button and then click on OK to close this dialog box.
Under the General tab, find the Database drop-down menu and select Pubs Database. The Master Database is usually selected on default, but users shouldn’t be allowed to access this database – it controls SQL Server installation settings.
Go to the Database Access tab which gives the available databases within the current server
A default database must be selected for SQL Server to enable any database roles. Select the Pubs database. The Public role is the automatically-assigned role as we mentioned earlier, but you can select any of the other roles. Note that the Guests Group must be the one selected under the User column
To complete the process, click OK. SQL Server Enterprise Manager should now display the new login ID on the right pane, and all members of the Windows group Guests can log onto SQL Server.