Tag Archives: ASK

SQLBits here I come

I recently found out that I am, unfortunately, not Charlie Bucket. The people over at SQLskills were offering a free ticket to one of their Microsoft Certified Master training camps dubbed SQLskills Master Immersion Events.

I entered into the competition on the off chance that I was picked and got to go to one of their week long training events. The gods were not smiling on me that day and I wasn’t chosen 🙁

However, this has not deterred me from continuing my quest for awesome training. I mentioned in my entry to the SQLskills competition that I would be going to SQLBits VIII. I have now completed all the preparations to be able to go there in April.

I will be attending the Friday training day and the Saturday community day and am looking forward to it. I know a couple of the people on the speakers list (the Saturday sessions are still open to voting, so the list may change yet) and hope to either catch up with them (Andre Kamman / Blog – we met at the PASS Summit in Seattle) or finally meet them in person (Matt Whitfield / Blog – we met online at ASK).

I am also hoping to meet up with a couple of other people from the ASK) site (in no particular order):

Jonathan Allen
Kev Riley
Thomas Rushton

They have all helped me a great deal in the last 6 months and I think a pint is in order! On top of which I would love to finally meet some SQL people from the UK, they are very thin on the ground where I live.

I also plan on making this a sort of reconnaissance mission for my employer. They recently made it clear that the PASS Summit is too pricey and I have been inofficially tasked with finding top-rated training a little closer to home. The videos of previous SQLBits conferences are very promising, so I expect things to work out well on that front.

I’ll be staying at the Best Western in Brighton, just down the road from the conference. As this is on my tab, The Grand was a little too much for my wallet to handle (maybe work will foot the bill the next time and SQLBits will be at the Ritz).

I look forward to meeting people and learning a few new things whilst I’m beside the seaside, beside the sea.

Hope to see you there.

Controlling access to database objects

I have been playing around with database security recently and a question over at ASK regarding controlling access to database objects has prompted this post.

It is regarded a best practice to lock down your SQL Server instances from unwanted access.  As SQL Server offers so many features, there are many facets of the system that need to be adjusted to reach the goal of a watertight system.

Removing user access to your tables is one of a number of ways of guarding your data.  If they cannot access the tables, then they have an extra roadblock in accessing and breaking your data.  Once this access is revoked, you can then go about granting access to these tables through other objects: vies, functions, stored procedures that give a layer of abstraction from the data and allow a tighter control over user access.

This would also allow you to build up a data access layer (DAL) that would move the database design towards the more traditional programming techniques applied in object oriented programming (OOP).  A DAL offers you the benefit of making structural design changes that are transparant to anyone or anything that is accessing the DAL, similar to interfacing between objects in OOP.

There are plenty of resources on this topic but this may give someone, somewhere a start off in the right direction.  Below is a test script that will create a test user, a table and a view that accesses the table.  The test user has access rights revoked to the table itself, but is allowed to access a subset of the table columns through the test view.

/* Create a test user without a login for this example only */

CREATE USER TestUser WITHOUT LOGIN ;

/* Create a test table */

CREATE TABLE dbo.TestTable

(Col1 int NOT NULL PRIMARY KEY CLUSTERED

, Col2 int NOT NULL

, Col3 int NOT NULL) ;

/* Deny select rights to TestUser */

DENY SELECT ON dbo.TestTable TO TestUser ;

/* Create a view that selects the first two columns of the test table */

CREATE VIEW dbo.TestView AS

SELECT Col1,Col2 FROM dbo.TestTable ;

/* Grant select rights to TestUser for the TestView */

GRANT SELECT ON TestView TO TestUser ;

/* Impersonate TestUser to inherit his access rights*/

EXECUTE AS USER = ‘TestUser’;

/*Try selecting from the base table – fails*/

SELECT * FROM dbo.TestTable ;

/* Try selecting from the TestView – success*/

SELECT * FROM dbo.TestView ;

/* Revert back to your access rights */

REVERT

/* Tidy up */

DROP VIEW dbo.TestView;

DROP TABLE dbo.TestTable;

DROP USER TestUser;