[WARNING! This is an archived post and as such there may be things broken/missing here.. you have been warned.]
Ok, as you all may be aware I have been wanting to put an entry into Roy's Add-in contest.
I decided to go ahead and tell you all what I'm trying to do. First of all, I've come up with a couple add-in ideas (none of which are possible in the short amount of time that I have); I know I've mentioned a few here... I'm sorry I'm not going to be able to do all of them.
Roy gave me the final piece of the puzzle here. Boy, does creating addins suck or what... I really wish COM was dead once and for all...
So here's what I'm going to try to do...
with the free time that I have (after preparing my house for sale and catering to my pregnant wife).
I'm going to produce an addin that let's you set permissions on tables, stored procs, and views in SQL Server. This will be a new window... I'm sorry, I'm not going to go nuts and try to hook into an existing database window (it's too much of a pain, and I don't have enough time). You will have to give this window a SQL server name and some info on how to connect to SQL server. Then you will be able to see all the databases in a tree and will be able to drill down to the stored procs, tables, and views in a particular database. Right clicking will give you a menu that will have (at this time) 1 option... “permissions...”
Clicking permissions will open a window with all the users and will let you set the permissions on the object.
What I have done
Roy's info has given me what I need to create a tool window. I already have figured out the SQL to query the permissions, etc (I've built these on SQL2k... hopefully it will work on SQL7 and Yukon). And writing a t-sql “Grant...” should not be that hard.
Now I just need to find the time... also I plan on making the SQL permissions piece be it's own class library that can be used in other projects... I'm hoping to donate it to some of the free SQL Admin tools that exist out on the net (permissions are a must).