In the famous words of The Rock “know your role and shut your mouth!”  Well mainly we were going with “know your role.”

Do you know your SQL Server security roles?

A SQL Server role defines exactly what a user can and cannot do inside a database.  It is also possible for multiple users be it AD users and groups or SQL Server users can share this role.  Roles are similar to AD groups in Windows security except  they only control security within databases.

Today we will discuss the fixed roles which are setup across the server instance and apply to all objects on the server.  They are as follows:

• SysAdmin Role: This is the Mac Daddy of roles as it can perform any and all actions on the server.
• ServerAdmin Role: This role can change configuration options on the server.  They can also shut down the server, choose them wisely.
• SetupAdmin Role: This role can manage linked servers, SQL Server startup options and tasks.
• SecurityAdmin Role: This role can only manage server security.  They can also reset passwords saving you from the mundane task, if you have your own minions.
• ProcessAdmin Role: This role has the ability to kill processes running on the SQL Server aka SPID killer aka Die SPID die!
• DbCreator Role:  This role can create, alter, drop and restore databases, which is pretty powerful just now Genie from Aladdin powerful.
• DiskAdmin Role: This role can manage the SQL Server disk files.
• BulkAdmin Role: This role can only run the bulk insert command (I have personally never seen used before).
• Public Role:  Every user belongs to the public role by default and therefore you cannot change membership to this role.  Keep in mind if you assign any permissions to this role it will apply across the entire instance.

There are several of these that I rarely use, but they are there if I need them.  Like the Rock says “know your role!”   Tomorrow we will discuss pre-defined database roles.  Enjoy!