DDL Change Capture Trigger

Question

DDL Change Capture Trigger

Adam Bean

One Orange Chip

Points: 26631
More actions
December 4, 2008 at 11:12 am

#128990

Hey all,
I created a database trigger awhile back to capture all DDL changes ... the problem was, that I was logging all the information to an 'admin' database which in most cases the requesting login did not have access to. While yes I could create a logging table in each database, that is a route I do not want to take.
So what I did was this ...
- Create the DDL trigger in all databases
-- The trigger will send the results of the EVENTDATA() to a procedure in the admin database, which will write the record to the admin database
- Created a procedure in the master database and marked it as a system stored procedure, and prefixed it with sp_
- Procedure has an EXECUTE AS 'dbo' (which actually I'm finding now to be a problem, when I was a user database, it wasn't an issue)
Yet I'm still back to a permissions problem. An account which does not have explicit execute on the procedure is failing to execute it ... yet it is a system stored procedure. Am I missing something here? I thought all logins have access to system stored procs (sp_who, sp_spaceused, etc.).
I have some options in terms of either creating a user per login to the admin database and giving write access to the table, or creating a user per login in the master database and granting execute to the procedure ... yet neither of these are desirable routes. I figured by taking the approach of a system stored procedure that I'd be able to resolve my permission issues.
So really I'm just trying to figure out what my best bet is at this point If I don't want to have to create users in either my user database or master database and then having to grant permissions. I was hoping execute AS 'sa' would work, but doesn't appear that you're able to do that?
Thoughts, comments, suggestions?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Use EXECUTE AS on the DDL trigger so that the trigger executes under the context of a login that has access to the admin DB and all objects involved. That way, you don't even need a system procedure.

If you need to see the person who caused the event, use the ORIGINAL_LOGIN function, as most others will get the impersonated login.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Good thought, put the execute as in the trigger ... yet being that I can't execute as sa ... what is my alternative if any here for an execute as to allow for cross database execution? Again I thought being a system stored procedure I wouldn't have this issue but now even my credentials are failing to execute when I have sysadmin ...

Thanks!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Create a login for this purpose and give it rights on the objects needed. You can give it a very long, complex password, since it's not intended to ever be used to login. Don't make is sysadmin. Grant explicit permissions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

Adam Bean

One Orange Chip

Points: 26631

December 4, 2008 at 11:38 am

#907828

Good point, that will definitely work.

Thanks

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

ARGH, so If I take this route, I'm still going to have drop this user in all databases (with the execute as in the trigger) ... again, not a big deal from the perspective of how I deploy my admin objects to my servers and all, but to the general community this is somewhat messy.

Any other thoughts? In the end I see this as being a trigger in all databases, one stored procedure stored in master - marked as a system object - with an execute as. Seems simple enough, yet still struggling. Why is it that I am having to have explicit execute permissions on a system stored procedure anyways? I just tested a login with nothing more than public in a user database and it can run all the system stored procedures. My procedure is definitely marked as a system object ...

I feel like I'm overlooking something small here.

Thanks

Adam Bean One Orange Chip Points: 26631 More actions · Answer 6

Actually, looks like I was overlooking something ...

After putting the execute as 'dbagent' back into the trigger and granting execute permission on the procedure, now I simply have the problem of dbagent not being able to talk to my admin database. So with that, technically this dummy account will only need access to master and admin. After doing this, I am up and running just fine.

Still though, I'd like to know why I have to have explicit permissions on a system stored proc...

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Just the admin DB. Unless there's a need to keep the proc in master?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Adam Bean One Orange Chip Points: 26631 More actions · Answer 8

Yeah, being that I still have to have explicit permissions on a user system marked procedure, there is no reason to keep it in the master database anymore.