Security Information and Review

Collection of security information and review

Fixed Database Roles in MS SQL Server Security

Author : Admin

The first step to securing MS SQL server security is Login Security. So, we will spend a lot of time ensuring unauthorized user never log into MS SQL Server successfully. The Login process is comparing account names and password in the list of authorized users, we need to prevent account name and password from being stolen by the wrong people.

In the MS SQL Server, there are some roles that represent server administrator tasks and we more granular control over what the administrator can do. We can classify of the fix role in the MS SQL Server : Primary Server Roles and Secondary Server Roles.

Primary Server Roles encompass the operations that most administrators will perform, we can refer to SysAdmin, ServerAdmin, SecurityAdmin and DBCreator. In generally, ServerAdmin, SecurityAdmin and DBCreator represent the sub categories of the system administrator’s overall duties.

The members of SysAdmin server role can perform any activity and have completes control over all database functions. One of the member SysAdmin is sa account, a unique account that it cannot remove from SysAdmin role and cannot be renamed. So, we must protect this account with a strong password.

The member of ServerAdmin role can change server configuration parameters but can not shutdown the server using the SHUTDOWN statement. Essentially, members of ServerAdmin have permission to run the sp_configure store procedure. In some case, server configuration parameters and setting only take effect after restart the MS SQL Server service.

The member of SecurityAdmin role can create and manage the process running in the MS SQL Server. The member of this role may run all security related system stored procedures, including sp_addlogin and sp_grantlogin. It’s also the only roles besides SysAdmin that may read the MS SQL Server Log. The member of SecurityAdmin also can manage and limit who can create a database by controlling the permission to run the CREATE DATABASE statement.

sp_helpsrvrole

The member of DBCreator role can create, alter and resize databases. This role seem as similar or duplicates one of the permission of the SecurityAdmin role, but actually has a different purpose and usefulness. The primary different with the SecurityAdmin roles, the member of DBCreator the only role other than SysAdmin that has permission to run the ALTER DATABASE statement. The member of SecurityAdmin can grant users permission to create a database but the users cannot alter the database’s size, change the location of it’s files and add files or file groups.

The secondary server roles has very little functionality. It’s very specialized purposes, such as SetupAdmin, ProcessAdmin, DiskAdmin and BulkAdmin. The member of the SetupAdmin role is useful for one purpose, managed linked servers and configure store procedure to run at startup. The member of the ProcessAdmin role, has capability to delete SQL Server process that are causing problems. The member of the DiskAdmin role has permissions to manage database disk devices. It can also run the sp_diskdefault and sp_dropdevices system stored procedures. The member of BulkAdmin roles has one special permission to run the BULK INSERT command. Interestingly, it does not grant its members any other permissions on the target table.

Get more information about the fix role of MS SQL server at Microsoft site. See next discussion about Fixed Database Role in the MS SQL Server.

Mohamad Widodo

Mohamad Widodo

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • del.icio.us
  • Netvouz
  • DZone
  • ThisNext
  • MisterWong
  • Wists
  • Technorati
  • YahooMyWeb
  • Slashdot
  • StumbleUpon
Categories: SQL Server Security
Nothing succeeds like the appearance of success.
15 November 09 at 05:18
If you like this posts, please leave messages / comments.