Tuesday, July 13, 2010

Permission confusion in MS SQL Server 2008

I’m singling out MS SQL Server 2008 though I’m sure this appears in previous versions as well.

Have you ever run into a situation in which, for some reason, you just couldn’t grant someone certain permissions in SQL Server?  If you’re irresponsible like me, you’ll generally just try to give them as much access as possible to make the problem go away sooner.

But there are an insane number of ways you have available to you to change permissions.  Do they all do the same thing?  Are they different windows into the same data, or do they affect different permissions?  Who the heck knows, except the SQL Gods.  Here’s an example of the overwhelming number of ways to assign permissions:

1. Select the Security->Users folder under your database item, and add/modify the property of users from there:

sqlhell_step01

2. Select the Security->Roles folder under your database item, right-click and select Properties on a database role and assign a user to that role.  Bewilderingly, this doesn’t appear to correspond to method #1!  In other words, if I assign a user to the dbowner role using method #1, they won’t be listed in the role membership listing of method #2!

sqlhell_step02

3. Right-click the database object itself, click Properties, then select the Permissions page.  OMG, now there’s a completely separate list of users and very fine-grained permissions which you can “Grant”, “With Grant” (whatever on Earth that means), or “Deny”.  Huh?  Why is there a completely different set of permissions?  Do these override the role memberships?

sqlhell_step03

4. Select a user from Security->Logins of the database server item, edit their User Mapping properties to assign them to a particular database, and from there modify their "Database role membership”.  This seems to correspond to the values in step 1 or 2.

sqlhell_step04

5. RIght-click the database server item and choose Properties, then click the Permissions page.  This allows you to select logins or roles and explicitly assign permissions similar to Step 3.  But you’re assigning these to Logins instead of Users?  Wha?  Does that override the User permission, or does the User value override the Login permission?

sqlhell_step05

 

I’m sure there are many more ways to modify permissions but I’d just be belaboring the point.  I have nothing against having multiple places from which to modify user permissions, but I’d like if there was some semblance of correlation to one another.  As it stands, when I assign someone permissions, I’m mostly just guessing.  I realize that this is a tool for hardcore administrators, but in reality it’s used by a lot of everyday programmer-users like myself.  We aren’t administrators nor do we want to be; we just need to do what we need to do to get our job done.

I have something I want to do and a cryptic way to accomplish this through the administration tool.  What I want is an easier way to specify what I want, and have the system be smart enough to guide me through (possibly multiple) options for making this happen.

No comments: