Post reply

Find Database permission changes made

  • October 24, 2015 at 2:18 am

    #304450

    Hello Geeks,

    I am having an issue to find the changed permissions of the database which is causing connectivity issues to the application teams. One of our DBA's had changed the permissions on all the databases mostly "db_owner" due to which many application login are unable to connect.

    I tried to find from the default trace but unfortunately, i am unable to find the trace file only for that date. I restored the old backups of the databases which were working earlier for them onto another instance and now i would like to compare them so that we can understand what all were changed.

    Any guidance is highly appreciated.

    Thanks,

  • October 24, 2015 at 3:11 am

    #1835338

    Not sure why you are digging in the past. Better to get things working again.

    But if you want to look, the primary places to for differences would be sys.database_permissions and sys.role_members. But the changes might have been on server level, in which case it's more difficult to investigate.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • October 24, 2015 at 5:15 am

    #1835344

    If the database permissions have been changed and the users are now connecting using db_owner permissions they shouldn't be facing any issues. On the other hand if you saying that he changed all Db_owner permissions into more fine grained access control he did the right thing from a security stand point.

    Unless you have an Audit I dont think there is any real way to check this. One way you might be able to find some details is by looking at the tlog using fn_dblog but there might be tons for data to filter thru.

    Jayanth Kurup[/url]

  • October 24, 2015 at 5:25 am

    #1835347

    Thanks for the replies.

    We now had these kind of issues reported twice with 2 different databases and we found out that one of guys have changed the db_owner permissions from a security standpoint. But some of those were required by application teams to be as is. Before we start getting more and more calls about remaining db's, we thought we would find what how it was before so that we can answer them accordingly or rather fix them the way they were originally.

    I am still trying the solution given and cross verify with database permissions and roles.

    THanks,

Viewing 4 posts - 1 through 3 (of 3 total)

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