Post reply

User losing permissions

  • August 4, 2010 at 11:41 am

    #141493

    A user consistently loses his read permissions to the database. I don't understand why that keeps happening. The user and I are losing our minds on this but I really can't think of a solution to the problem.

    Would it be possible for someone to create an SP which checks if the user has permissions. If he doesn't then it will have to map the permissions. I'll run this as a job every 30 mins.

    Thanks eveyone.

  • August 4, 2010 at 12:23 pm

    #1202301

    things to look for:

    1. the user would lose his permissions every time the database is restored to a version that did not have his permissions previously.

    2. if this is object level permissions, ie for specific tables or views or procs, if the object is dropped and recreated for any reason, the user would lose his permissions due to that as well. ALTER of the objects would maintian permissions, if that is what is happening.

    3. if his permissions come from a role, if the role is dropped and recreated, the user needs to be added back tot he role, or else he loses the permissions like you are saying.

    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!

  • August 4, 2010 at 12:59 pm

    #1202328

    And there's always the weird stuff. We had a developer accidently drop his access to one stored procedure because he wanted to test security so he revoked his privs, but did it in the prod definition. Every time he deployed the proc it yanked his privs. Took us a couple of days to find the problem.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 4, 2010 at 1:19 pm

    #1202342

    You may also read the Default Trace during the time the user loses his permission.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • August 11, 2010 at 6:16 am

    #1205300

    The user loses permissions on the database when it is restored. The database user to SQL login mapping goes for a toss in many instances... Check if that is the reason for the loss of permissions.

  • August 12, 2010 at 1:58 am

    #1205837

    Create database level DDL trigger to track when the permission is changed and who did it.

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

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