Post reply

any way to determine when a GRANT was done...

  • June 15, 2012 at 12:20 pm

    #258174

    We have a couple of users that were put into the db_owner role. We are trying to find out when it was done.

    Are there any queries that could be run against SQL Server's catalog to identify when a GRANT was done, or when a user was added to a role? Things like sp_helprotect don't give that kind of detail. Perhaps a DMV?

    Thanks.

  • June 15, 2012 at 12:39 pm

    #1501846

    The only way I can think of is if you had auditing setup. After the fact, I don't think you are going to have much luck.

  • June 16, 2012 at 9:09 am

    #1501963

    From my recent experience this is done using a DDL (data definition language) trigger. Of course, it would be only going forward after you set it up, I don't know how you can find out if no trigger was set up

  • June 18, 2012 at 7:29 am

    #1502225

    Thanks - wonder why this isn't tracked in the catalog. We are going to implement some triggers to capture thi going forward.

  • June 18, 2012 at 7:42 am

    #1502233

    john.p.lantz (6/18/2012)

    Thanks - wonder why this isn't tracked in the catalog. We are going to implement some triggers to capture thi going forward.

    there's a LOT of things that could be built into SQL server to do automatically, but are left for the end users to decide if it is apprropriate for their business needs.

    everything from automatically taking backups, automatically tracking changes, different kinds of automated logging, etc.

    i think the logic is they simply provide the tools,and you need to decide which you need for your particular business model.

    Logging that you think would be great might not fit with the logging model I think i need.

    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!

  • June 18, 2012 at 10:33 am

    #1502325

    wonder why this isn't tracked in the catalog.

    I suspect it was to keep down overhead.

    MS could theoretically have logged tons of things like that in SQL, which would be useful in very selective situations, but the overall overhead on the system would be quite high, and most people would likely have never used much of the data being captured.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 6 posts - 1 through 5 (of 5 total)

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