When it comes to managing a SQL Server installation, it is important to have a system in place that allows for the delegation of administrative tasks without compromising security. This is where fixed server roles come into play. Starting with SQL Server 7.0, Microsoft introduced several pre-defined fixed server roles that have different permissions and are designed to distribute administrative responsibilities.
The fixed server roles available in SQL Server 2000 are:
- bulkadmin
- dbcreator
- diskadmin
- processadmin
- securityadmin
- serveradmin
- setupadmin
- sysadmin
These roles are extremely useful when it comes to delegating specific administrative tasks to personnel without granting them complete control over the SQL Server installation. For example, if an organization wants a junior DBA to handle security on its SQL servers but doesn’t want to give them full administrative rights, the securityadmin fixed server role can be assigned to the junior DBA. This allows them to carry out their security responsibilities without having complete control over the SQL Server installation.
Each fixed server role has its own set of permissions and limitations. To determine the rights of each role, the system stored procedure sp_srvrolepermission can be used. However, it’s important to note that this stored procedure may not show all the rights a particular fixed server role has. In such cases, it is recommended to consult the SQL Server documentation for complete information.
Let’s take a closer look at some of the fixed server roles:
The bulkadmin role
The bulkadmin role was introduced to allow non-administrative users to use the BULK INSERT command. Previously, only members of the sysadmin role had the ability to execute BULK INSERT. With the bulkadmin role, users can use BULK INSERT as long as they have appropriate rights to the table in question. This role provides flexibility to the DBA by allowing them to delegate the use of BULK INSERT without granting sysadmin rights.
The dbcreator role
The dbcreator role has the ability to create, drop, and restore databases. When a database is created, the creator automatically assumes ownership. This role is useful for junior DBAs who need the ability to create and maintain databases without having access to global SQL Server configuration options or external security settings.
The diskadmin role
The diskadmin role is responsible for managing backup devices. Members of this role can add and remove backup devices, but they do not have permissions to backup a database by default. Additional permissions at the database level are required for backup operations.
The processadmin role
The processadmin role has the ability to issue the KILL command, which allows them to stop a query running within SQL Server. This role is useful in scenarios where an orphaned connection is holding a lock on a resource or when a runaway process needs to be stopped to free up resources.
The securityadmin role
The securityadmin role is used to manage user accounts within SQL Server. Members of this role can create and drop logins, but they do not have permissions to assign database permissions. To allow a login in the securityadmin role to grant users access to a database, the login must be mapped to a user in the db_accessadmin fixed database role for that database.
The sysadmin role
The sysadmin role is the most powerful role in SQL Server. Members of this role have complete control over the SQL Server and can perform any administrative task. It is important to grant sysadmin rights sparingly and only to individuals who require full control over the SQL Server.
It is worth noting that the sysadmin role bypasses security checks and overrides any deny permissions. Therefore, it should be used with caution and only when necessary.
In conclusion, fixed server roles in SQL Server provide a convenient way to delegate administrative tasks while maintaining security. By understanding the permissions and limitations of each role, DBAs can effectively distribute responsibilities and ensure the smooth operation of their SQL Server installations.