Tip

SQL Server security: The implications of user-defined roles

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

    Requires Free Membership to View

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
CREATE SERVER ROLE remote_admins AUTHORIZATION s_yegulalp

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

GRANT CONNECT SQL TO [remote_admins]
GRANT VIEW ANY DATABASE TO [remote_admins]
GRANT ALTER ANY DATABASE 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.

This was first published in September 2012

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.