SQL Server Fixed Roles And Related Stored Procedures

Good day everyone and welcome back to SQL Something!

Do you like Roles? Do you want to know a little more about SQL Server's built in Roles and what they do? Do you want to know some nifty built in sprocs that also give some extra info about the Roles?

Then this is a post for you!


Firstly let us look briefly at SQL Server's nine (9) built in Roles:

• SysAdmin - This is the big guy. Members of this Role have all the permissions across the instance. A SysAdmin is specified during installation.
• ServerAdmin - Members of this Role are allowed to perform instance configuration tasks and can also stop an instance.
• SecurityAdmin - Members can alter and elevate permissions for Logins. Can elevate permissions to SysAdmin.
• ProcessAdmin - Members can stop instance processes.
• SetupAdmin - Members can add/remove DBs to linked servers.
• BulkAdmin - Members can bulk insert.
• DiskAdmin - Members can perform tasks involving instance related files.
• DBCreator - Members can add/drop/alter/restore databases on an instance.
• Public - The little guy. All logins are considered part of the public Role group. Not a 'real' built in role.

Next, we will examine a couple stored procedures that add to the above info.


sp_helpsrvrole:

• Description: Returns the names of all fixed Roles and a brief description of each or if given a role name as a parameter, it will return only that Role's name and description.
• T-SQL Example:  EXEC sp_helpsrvrole;
• Output: Fig. 1.

Fig.1: sp_helsrvrole output...

sp_helpsrvrolemember:

• Description: Returns all members of all fixed Roles or if given a Role name as a parameter, it will return only the members of that Role.
• T-SQL Example: EXEC sp_helpsrvrolemember 'sysadmin';
• Output: Fig. 2.

Fig.2: sp_helsrvrolemember output...

sp_helpsrvrolepermission:

• Description: Returns all permissions that each fixed Role has or if given a Role name as a paraameter, it will return only the permissions accessible by that given Role.
• T-SQL Example: EXEC helpsrvrolepermission 'BulkAdmin';
• Output: Fig. 3.

Fig.3: sp_helsrvrolepermission output...

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)