Category: Security

Jana Sattainathan explains the benefits of using schemas to segment out functional sections of a database:

My recommendation on how to manage permissions goes like this:

1. Create Database with appropriate Schemas – like HR/Finance (or) Staging/ETL etc
2. Create objects like tables and views inside the appropriate Schemas
3. Create database roles such as db_finance_admin_role, db_developer_role, db_ddl_deployer_role etc
4. Grant permissions at the Schema level to database roles as shown in the example above
5. Create AD groups (instead of individual logins) like Finance_DB_Admins, IT_Developers etc
6. Grant database role membership to AD groups instead of individual logins – EXEC sp_addrolemember N’db_developer_role’, N’IT_Developers’

Doing it this way allows you to separate the concerns. For example the db_developer_role can be granted more or less permissions and all the groups granted that role will automatically get that. Also, you are free to use the AD groups across instances in multiple databases with different permissions.

Click through for more details, including how to get to separate schemas from an all-dbo database.

Monica Rathbun finds trouble with Always Encrypted:

The real challenges started when the client began to test their application code. The first thing we hit was triggers.

The table had several insert triggers associated with the columns that were now encrypted. Since the data was now encrypted the insert triggers would fail. Again, we lucked out and they were able to recode somethings in order to remove the triggers. Of course, since troubles always come in threes, this was no different. First the constraint problem, then the triggers, then we hit the biggest road block that halted our Always Encrypted implementation.

Read on for more information about the things you cannot do with Always Encrypted, including some limitations which will eventually go away.

Bert Wagner hits on the most common reason why you might fail to connect with a SQL authentication login:

I thought it would be best to start with a clean slate so I created a new SQL login and database user so that I could definitively figure out which permissions are needed.

Normally I use Windows Authentication for my logins, but this time I thought “since I’m getting crazy learning new things, let me try creating a SQL Login instead.”

After I created my login, I decided to test connecting to my server before digging into the permissions. Result?

After the fifth or sixth time it happens to you, you start making that the first thing you check.

Eric Blinn shows that dynamic SQL in stored procedures changes the security paradigm a bit:

Security was controlled by granting EXECUTE permissions only to appropriate stored procedures and by explicitly not granting permission to any tables or views within the database.

One of the procedures was getting a bad query plan and timing out. This is when I was called in. The procedure was performing a search based on an unknown number of up to 10 search parameters. The code was filled with many AND/OR combinations to account for the users’ ability to include any combination of search parameters. I found this procedure to be a prime candidate for dynamic SQL where I would create the select statement including only the search parameters entered by the user into the WHERE clause.

Immediately upon testing the users started to receive SELECT permission denied errors. It turns out that when you change to dynamic SQL and call your statement through sp_ExecuteSQL that the permissions are lost. Our options were to grant explicit select permission on the objects or to refactor the code such that it does not use dynamic SQL anymore.

The best solution here would probably be to use a certificate to sign the procedure and give that certificate user rights to select from the tables used in dynamic SQL.