Continuing on last week’s post on security principals, this week’s installment in the series on SQL Server security takes a look at securables.
There are a number of layers in the SQL Server security model, giving you a nearly infinite number of ways to set up access control on your server and databases. Security is a huge topic, and there are literally entire books on it, so this series of articles is designed to give you just a quick overview of the SQL Server security model to get you started.
In this first installment, I’ll go through the different types of security principals that are available, as well as how they connect to each other.
Basic model changes when you’ve built your solution can be tricky, because they can require redesigning or rebuilding an entire solution. Sometimes, though, the solution can be pretty easy. Like changing an object’s schema, a task that can be done using the ALTER SCHEMA statement.
Application roles provide a practical way to assign application-specific permissions in your database and to make sure that your applications always use a defined login. Not to be confused with actual roles, application roles are more like users in the database.
Ever wished you could decrypt a database object in SQL Server? The good news is, you can, even in newer versions of SQL Server! This article will take you through the basics of how to decrypt a database object, and it will hopefully give you some deeper knowledge of how encrypted objects are stored in the database, and how to access them.
When you install SQL Server, you need to specify what account(s) or group(s) that you want to give administrative privileges. Way back when, it would be sufficient to be a local administrator on the Windows machine running the SQL Server service, but not any more.
Luckily, there’s a solution.
Whenever you run dynamic SQL code from an application or in a stored procedure, make sure you clean (called “escaping” in developer-speak) all those apostrophes and semicolons, or you may find yourself on the business end of an SQL injection.