SQL Server Fixed Server-Level Roles

SQL Server’s fixed security roles provides powerful functionality for granting delegated access for administering your SQL environment, however contrary to popular usage, these roles are not intended to be used for application level access to your SQL Server databases, which is why I thought it might be good to provide a quick review on what each of these roles is and the functions which they perform.

You can get a quick list of SQL Server fixed roles through the following query;

SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;

Once armed with this information, the below screenshot from Microsoft’s own reference article explains what access each role provides (http://msdn.microsoft.com/en-us/library/ms188659.aspx);

Remember, play it safe and always adhere to proper role based security assignments to keep your environment under control and easily managable!

The SQL Server Support Team
DBA SERVICES