Month: August 2016

Create View Permissions

CREATE VIEW Permission

I’m mainly writing this as documentation for myself as, in the end, this is the original purpose of this blog, to document SQL Server and new aspects of it that I learn and try.

Personal Template

I’ve always had a little block with regard to this as, for database permissions, I always followed a template in my head:

USE <database>
<Give/Take away> <what permission> <On What> <To Whom>

It’s The Little Things That Trip You

With CREATE permissions this isn’t the case; there is a piece of the above template that isn’t needed, and it’s quite easy to see why when I sat down and thought about it.

Specifically, it’s this bit:

<On What>

I’m granting CREATE permissions; since I haven’t created anything, I can’t grant the permission on anything.
So for CREATE permission, I have to modify my template a bit:

USE <database>
<Give/Take away> <what permission> <To Whom>

If I use this now as a template to a GRANT CREATE VIEW, it will work:

USE [localTesting];
GRANT CREATE VIEW TO [testUser];

And it works!

[SQL Server] Efficiency of Permission Granting.

Words: 349

Reading Time: ~1.5 minutes.

The lead up

Recently I was asked to create a temporary user with SELECT permissions on a database.

So far, not a problem. Taking advantage of the pre-defined roles in SQL Server, I just add this new user to the pre-defined role [db_datareader], which grants SELECT permissions to all tables and views in a database.

Why would I grant SELECT permissions this way and not manually choose the tables and views that this user could connect to?

Well,

  1. This is a test server so there is no sensitive information held that I’m worried about blocking access to,
  2. I didn’t get the exact requirements of the tables this user is to query so I don’t know which tables/views to grant access to and which to deny access to (I consider this a mistake on my part and something I have to act on next time),
  3. The test user is only required for 2 days, after which it is getting reviewed and deleted as quickly as I can, and
  4. Efficiency.

Efficiency, how?

Why grant SELECT on tables individually when I can grant on all tables in 1 fell swoop?

In the same vein, hypothetically speaking, if I was asked to grant SELECT permissions on 96 out of 100 tables, I would GRANT SELECT on all of them and then DENY SELECT on the 4 required as long as no column-level GRANTs have been given on those tables.

 Summary

A recent notion that came to me was that one of the roles of a DBA is to gather knowledge, but to a level that promotes efficiency.

Sure, we know how to grant permissions, but we should also know the pitfalls, such as “deny beats grant unless the grant is on the column level” or “there are some combinations of permissions that allow more than intended“.

Knowing these caveats allows us to say when options can be automated or where rules need to be added to check for different statuses.

This will allow us to move on to the next aspect that needs a DBA’s eye and gentle guiding touch…or 2 cups of coffee and a full throttling !

[SQL Server] How Well Do You know Your sys Tables? Test yourself

I may have been too reliant on Intellisense…

Intro:

Reading Time: ~1 minutes

Recently I had to check on the nature of my check constraints and foreign keys; whether they were trusted or not.

select name, is_not_trusted from sys.check_constraints;
select name, is_not_trusted from sys.foreign_keys;

In case you are wondering, this has some Query Optimiser (QO) benefits so it’s a definitely a bonus to ensure that they are trusted.

However, something that should have taken 5 seconds and be a no-brainer, took me 30 seconds and required a bit of memory power on my part.

All because I had a problem with my SQL Server Intellisense, and said SQL Server Intellisense stopped working.
This forced me to drudge up these names out of my memory from whatever blog post or BOL entry I read them in.

This, in turn, got me wondering; how well do you really know your sys tables?

If your intellisense broke tomorrow, would you know your Dynamic Management Objects (DMO)?

Now before you dismiss this notion as simple, remember that it is not just sys tables that you have to know. This will test your knowledge on your application tables, your columns, stored procedures and functions.

Do you know all their names? Which table has the column “ID”, which has “<table_name>ID” and which has “<table_name>_ID”?

Test yourself:

The fix for my problem with SQL Server Intellisense not working is going to be reversed to allow you to test your knowledge.

In the dialog box, Tools -> Options -> Text Editor -> General, there are two checkboxes under the “Statement completion” section:

  • Auto list members
  • Parameter information

If you uncheck these two checkboxes, your intellisense is gone!

TransactSQLGeneral

Try it out, even for 5-10 minutes.

Hopefully, with your Intellisense gone, your sense will remain.