stored procedure creator

  • I have been blessed with the task of administrating my company’s databases; to put it nicely you can say the task fell into my lap.

    Anyway, I have been looking through the databases and there a lot of objects that just appear out of now where and I would like to know who is creating them. I tried the social engineering way and by asking the developers and no one is claiming the crap that is polluting the databases.

    Is there anyway other than setting up a trace to find out who is creating objects? We are running MSSQL 2K sp4.

    Thank you

    Mike

  • there's pretty much only two ways; setting up a trace, which you stated you wanted to avoid;

    you'd need to set up a trace to capture TSQL statement completed, and then search the results for CREATE.

    the other is using a log trolling software (they all offer 30 day free trials), which only works if your database is in FULL  and not SIMPLE or BULKED LOG mode; if you are not in FULL mode, you could change it and try and capture.

    options: 1.what kind of objects are being created? if you gave the names here, someone might be able to identify the software.

    2.since you suspect it might be a software package doing this, change passwords for the users. this might make the package crash and help identify the culprit.

    3.maybe you can change any of the roles the possible logins belong to;changing them to be db_datareader and db_datawriter, and granting execute to procedures and functions.... instead allowing the role of db_owner; that would take away their ability to create objects like tables,proces, etc, and someone might complain and help find the item.

    4. change the sa password, and the Administrator password, if it's not going to screw up your scheduled jobs too much; maybe some developer is running something as sa.

    In 2005 you can set up triggers on create object events i believe; that's a posibility for a future issue.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

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