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

SQL Server security made simple and sensible

The many layers of SQL Server security can be confusing. Learn to connect to SQL Server and retrieve data from databases, as well as the ins and outs of object ownership.

Security in Microsoft SQL Server is flexible, offers a lot of options and remains compatible with nearly decade-old...

security models. But its multiple layers may confuse database administrators.

Get connected

The first level in SQL Server security is getting permission to connect to the server. This permission is granted by a login.

SQL Server offers two distinct modes for logins: Windows Authentication and Mixed Mode.

More on SQL Server security

FAQ: How to troubleshoot and grant SQL Server permissions

SQL Server security: Controlling access via database roles

 In Windows Authentication mode, logins are created by specifying Windows users and groups – either local ones from the SQL Server computer or those from the Active Directory domain. Anyone whose user account is made into a login – or who belongs to a group which is a login – can connect to the SQL Server.

Mixed Mode is a combination of Windows Authentication and local authentication. While logins can still be created from local or domain users and groups, you can also create logins entirely within SQL Server, assigning passwords SQL Server will validate.

In either mode, once connected via your login, you can perform certain server-wide tasks. Exactly which tasks depends on the pre-defined server roles a login belongs to. Server roles bundle up a variety of permissions, such as permissions to create new databases, back up databases, restore databases, and so forth. The permissions assigned to a server role are fixed, and custom server roles cannot be created. Simply drop logins into the appropriate roles -- and you're done.

Getting to data

A login doesn't provide access to SQL Server's databases. In order to use a database, a database user needs to be created inside the database.

Database users are mapped directly to logins; a login gets you into the server, and a database user gets that login into a specific database. Database users aren't shared across databases. Therefore, if you want to access two databases, you'll need a database user within each.

It's possible to create a database user that maps to a Windows-authenticated group login, like Domain Users. In this case, the login allows anyone who is a member of Domain Users to connect to the server, and the database user allows anyone in Domain Users into that specific database. So a database "user" can represent multiple human beings.

The only real purpose of a database user is to assign permissions within the database. This is done by dropping the database user into one or more database roles, either predefined ones set up by Microsoft or custom ones that encapsulate whatever permissions you desire. Predefined roles like DatabaseReader and DatabaseWriter are pretty self-explanatory; custom roles can be created when you need a set of permissions that isn't covered by a predefined role.

It's also possible to assign permissions directly to individual database users, but this isn't recommended because it makes ongoing maintenance more tedious and difficult.

A question of ownership

The last tricky bit in SQL Server security is permission chaining.

Permission chaining is built around the concept of object ownership: within SQL Server, each object – a table, a view, or a stored procedure – is owned by a particular user. Often, the object owner is the pre-created "dbo" user, which exists by default in every new database and is mapped to the Administrator login.

SQL Server only checks access permissions within a database in two instances:

  1. When an object is first accessed.
  2. When the ownership chain changes.

Therefore, imagine you have a stored procedure that accesses a table. Both are owned by a dbo. You have direct permissions to use the stored procedure, but you don't have permission to access the table. When the stored procedure is run, SQL Server checks your permissions and allows you to run the procedure. Because the procedure and the table are owned by the same user, the ownership chain is unbroken and SQL Server doesn't check your permissions to access the table. If you tried to access the table directly, however, you would be denied.

Permission chaining is useful because it allows control over how users can access data, like only through a stored procedure (as in the above example). By allowing users to access data through controlled interfaces like stored procedures and views, you control what they can do with the data.

Multi-layered but straightforward

There are some things you can do to simplify SQL Server security:

  • Stick with Windows Authentication mode whenever possible.
  • Add logins for domain groups, not users.
  • Use predefined database roles as much as possible, and never assign permissions directly to a database user.
  • Let dbo own everything, and grant permissions primarily to "front-end" objects like views and stored procedures.

These simple tips can make SQL Server security a lot more approachable and understandable.

Miss a tip? Check out the archive page for Don's series, SQL Server for the Reluctant DBA.

ABOUT THE AUTHOR:
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at www.ConcentratedTech.com.

This was last published in October 2009

Dig Deeper on SQL Server Security

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close