As I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

Security question

The next question in the list is:

What are some tips regarding roles other than sysadmin?

This is another security question that I like.  I like it because it looks at and considers roles other that sysadmin for users.  Often I see instance and database security configured in a security vacuum that only leverages sysadmin and db_owner.  Of course, not all of the other server roles are that useful.  Before tips on leveraging the other server roles, we need to know what those other roles are:

• bulkadmin – Ability to execute BULK INSERT statements
• dbcreator – Ability to create, alter, drop, and restore databases with no restrictions
• diskadmin – Ability to manage disk files used during backups.
• processadmin – Ability to kill processes on the SQL Server instance, aka spid-killer.
• public – No abilities granted by default. Everyone has access to this role.
• securityadmin – Ability to reset logins and set permissions at the server and database level.
• setupadmin – Ability to add and remove linked servers.

Server Role Tips

• The bulkadmin role can be useful for accounts performing ETL or ELT operations.  If the process needs BULK INSERT access, this is the best method to provide that access without granting excess permissions.
• A common recommendation I’ve heard for dbcreator, over the years, is to grant access to this role for applications that need create databases when they are installed.  Since the role has the ability to drop any database on an instance, it is not actually ideal for that purpose.
• A good use for dbcreator is to grant it to DBAs that you want to grant limited access to the server.  It provides coverage for quite a few of the core duties of DBAs without granting full server access.
• The diskadmin role is about the most pointless role, don’t use it.  It’s limited to such a small set of permissions that don’t allow the grantor to really accomplish anything.
• The processadmin role has less permissions that sysadmin, but is nearly as powerful.  If someone requests this, treat requests for this role the same as you would from sysadmin.  Do you really want users killing spids?
• The public role is the only role that can be granted permissions that everyone with server access would inherit.  Don’t do this.  The abuse, or use, of public can cause lots of headaches.
• The setupadmin role is another that has limited usefulness.  While it does provide access to the ability to create linked servers, this is likely something that DBAs with more permissions would be deploying.
• Treat securityadmin similar to sysadmin.  Since it can grant most server and database permissions, it is able to grant access to virtually everything sysadmin has access to.

Summary

As shown, there are some useful other server roles available, such as bulkadmin and dbcreator.  Most of the roles, though, are fairly limited in their usefulness.  It’s important to know them all and their limitations.  Because, while you might be stringent on sysadmin, the clever developer might ask for the securityadmin role, leaving you unaware that the keys to the castle were just given away.  Any other tips for leveraging server roles that you think people should know?