Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server security: The implications of user-defined roles

SQL Server security is a topic on many DBAs' minds. But what are the implications of user-defined roles? Expert Serdar Yegulalp explains.

Roles in SQL Server have been around in one form or another for almost the entire lifetime of this product, but SQL Server 2000 introduced the server-wide roles and security that we're most familiar with today.

Databases also had their own roles, which we could use in out-of-the-box form or customize as needed. This was handy in situations where SQL Server and SQL Server security were used in a far more granular or complex way than usual, or when you needed to devise a custom behavior to handle this.

More on SQL Server security

Don't miss these basics on SQL Server security

SQL Server security explorer

Up until recently, though, there was no way to create your own custom server-wide roles -- just roles for individual databases. Server-wide roles could be modified by changing the permissions available to each role, but that could come back to bite you if you weren't careful. For example, if you expected a given role to not have certain permissions and relied on that when giving a contractor or other outsider access to the server, you would be leaving the server quite vulnerable. Likewise, you could assign a user account to a given role and then manually assign permissions to that account, but that was a management headache as well.

SQL Server 2012 finally deals with this problem by allowing the creation of custom-defined server-wide roles -- which include not just modifications of the existing roles, but entirely new ones. This is done via the CREATE SERVER ROLE command and its cousins:

USE master

To add permissions in the role, use the GRANT commands:

GRANT CONNECT SQL TO [remote_admins]

Note: There may be other permissions you need to add; this list is just an example.

Finally, add users using this method:

ALTER SERVER ROLE [remote_admins] ADD MEMBER [d_low]

Finally, to remove a role entirely after you've cleared it of members, use the following command:

DROP SERVER ROLE [remote_admins]

Server-wide roles can grant an unprecedented degree of power to the users allowed to connect through them -- so much, in fact, that at first it's tempting to use this as a shortcut for situations where you just want to temporarily give someone access to everything, under the pretext that you can always revoke it later. There are two problems with this assumption:

 1) It's easy to forget to revoke privileges; and

 2) It's easy to forget that some behaviors should be constrained to a single database and no further.

When creating server-wide roles it's best to begin with the absolute minimum privileges needed -- not just behavior within the database engine, but also outside it. For example, if you need to give someone read access to all of your databases at once, it makes sense to create something like a remote_readonly account and equip it with not only the needed permissions but also the needed endpoint connections. This locks the account down a little more.

Another downside to user-defined roles is that you have to be explicit about the permissions granted to a given role. It can be easy to forget some of the functions and privileges that might be required of a given role -- for example, the ability to alter a trace, an event session, or something similar. To that end, make sure that any roles you create from scratch can have all their needed functionality thoroughly tested, possibly via automation, before you put them into production use.

Additionally, keep in mind that SQL Server 2012, like previous versions of SQL Server, has entirely new sets of permissions available to it -- 19, according to Microsoft. If you haven't already studied up on those changes, learn about them so that any server-wide permissions you grant are used as effectively as possible. As a side note, server-level auditing has been expanded to include all editions of SQL Server, including Express, so that's another functionality that can be managed through a custom server-wide role.

Another nice thing about custom server-wide roles is that they're not solely a professional-level feature. SQL Server 2012 Express Edition supports custom server roles, so if you're using that iteration of the product you won't have to pay to use this feature.

Finally, remember that the syntax for creating roles and adding members is now done entirely through the CREATE/ALTER/DROP SERVER ROLE or ADD MEMBER commands. Any stored procedures you might have used to perform these actions, such as sp_addsrvrolemember or sp_addrolemember, are being deprecated. Make sure any existing code you have (such as scripts or stored procedures) is fixed to reflect this before you upgrade to SQL Server 2012 and start making use of the new server-wide roles functions. The changes in the newest version of SQL Server will be very positive for SQL Server security.

Dig Deeper on SQL Server Security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.