Problem solve Get help with specific problems with your technologies, process and projects.

Checking new tables for keywords, then adding permissions

I recently saw a question posed by a user wanting to create a trigger on the system table 'sysobjects'. As I've found, and as you mentioned, it is not possible, and apparently "there is no workaround". My problem is that I need to add certain table-level permissions any time a table that contains a specific keyword is added to a database. In a perfect world, I would have a trigger on the sysobjects table in the database that fires every time a table is added, checks to see if the table contains the keyword, and if so, it runs a script that will apply the permissions. However, I haven't been able to figure out a way to capture the event that occurs when a table is created. I am completely spent looking for a solution and would REALLY appreciate any insight, alternative, or help that you could offer.

The only way I know of is utilizing 3rd party tools and a LOT of coding. Install Lumigent's Entegra and set it up to send notifications to a specified mailbox when tables are created. Now create a service that will run to pull any new message arriving in that inbox, scan the content of the message that Entegra sends back for a table being created matching your string, and when found, initiate a connection to your database server and execute a script that you have coded into the service.

What you are asking for, while possible, really worries me. It tells me you have an environment where there is aboslutely no control on the creation and destruction (if you have the authority to create, you have the authority to drop) of database objects since they can apparently be done at a whim. This is a formula for some extremely serious issues eventually as you have a completely unmanageable environment where a DBA has no ability to understand what is and isn't in a database from one minute to the next.


For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.