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.
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 use...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
r 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:
- When an object is first accessed.
- 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.
[IMAGE]
[IMAGE] SQL SERVER FOR THE RELUCTANT DBA
[IMAGE] Part 1: How SQL Server really works
[IMAGE] Part 2: Understanding backup and recovery
[IMAGE] Part 3: Optimizing indexes
[IMAGE] Part 4: SQL language crash course
[IMAGE] Part 5: SQL Server security made simple
[IMAGE] Part 6: High-availability options and caveats
ABOUT THE AUTHOR:
[IMAGE]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.
