The client I'm working with at the moment has decided to push their security settings into the database.  By that I don't mean that they are setting up a special table for permissions or anything, they are actually using the built-in security in the database to control access to the data.  At first I wasn't convinced this was a good idea, since the more normal N-Tier methodology was to simply lock everyone except the data layer out of the database, and then let either the data layer or the business logic layer handle who could do what.

The problem with relying on the DAL or the BLL to secure the database is that Microsoft has been making it easier and easier for non-technical people to access databases.  Where you used to have to enter a long arcane connection string using a specialized program to access a SQL database, now it is as easy as 3 clicks of the mouse in Excel to connect to a remote database.  If the users ever find a valid username and password to get into the database, they can play at will and you won't even know they are in there.  So what do you do if you have users who like to play?  You lock them out using their Active Directory login.

The client has configured their databases to only use integrated authentication, there are no SQL logins (except SA, which is password protected by the DBA).  So anything you want to do has to be granted to you by your AD group membership.  Therefore it doesn't matter how you get into the database, be it using the application that we wrote or one that they hacked together on their own, they still can't do anymore then then they can from inside the application.  So one problem solved.

The problem that using SQL security causes is that it is not immediately accessible by the UI.  So that leaves the UI developer with the option of leaving the Save button enabled and then telling the user that they can't save the record after they try clicking on it, or else finding some way to query the database to see if the update or insert is going to succeed when the user opens the form, thereby allowing the UI to disable buttons that are not going to work anyway.  We decided to see if we could convince SQL to give up its secrets.

Luckily Microsoft anticipated this requirement and provided a stored procedure which makes it fairly painless to do this.  While the security is stored in several of the system tables which could be queried directly, since MS spent the time to make a nice neat stored proc, I figure we might as well make use of it.  It is called sp_helprotect.  (Note that there is only one "p" in that word...)  If you just call the stored procedure with no parameters it will return a list of all of the objects that you have rights to.  However, if you pass in a table name, or a view name, or a stored procedure in as the first parameter, you can get just a list of the rights (Insert, Update, Delete & Select) that pertain to that object.  Note that the Select and Update may make use of an additional column in the resultset which is "Column", since you may have select rights to all of the columns, but you can only update a few of them.  If column-level security is used, you will get a row for each column for both update and select permission types.

There is also another stored procedure that is called sp_helprolemember which will tell you if the user belongs to a specific role in SQL (not Active Directory).  This is useful if you have non-data related security that you want to create, for instance a CanPrint role which allows users to print a page.  This stored procedure also accepts the role you are looking for as the first parameter, but I never use it since instead of coming back with zero rows if you don't belong in the role, you instead get a SQL error thrown.  It's cleaner to get a list of all the roles (using no parameters) and then step over them to find the role you are looking for.