Found a bug with Roles

  • The scenario:

    SQL 9.0.4340, administering with SSMS 10.50.4000

    I grant a developer DataReader, DataWriter and DDLAdmin on several DBs on this development server.

    User complains of not being able to rename an stored procedure, as they should, being in the DDLAdmin role.

    Check this with Exec As and get and error that object does not exist or user does not have permission.

    Look at user account in that DB with SSMS and see it has DataReader, DataWriter and DDLAdmin checked, but disabled. Also see that this user owns the DataReader, DataWriter and DDLAdmin roles (also checked but disabled). Do not know why this is because users are never given ownership of any schemas when being added to the DB, period. In cases where a developer needs to put rights statement into their scripts they are given control over given schemas in Dev, but that’s as far as it goes.

    To fix I changed ownership of all schemas owned by user to dbo then the user no longer had any of those roles. User was granted those roles again and now works as it should.

    On another DB on the same server, same problem exists. Further investigation reveals that querying the user rights returns same info as SSMS GUI except that I don't see the the role entries are "disabled" as I do in SSMS.

    I look at the role properties by themselves for each, DataReader, DataWriter and DDLAdmin and see that it is owned by DBO. In one DB I changed the owner of DDLAdmin from DBO to DDLAdmin and then the user side was fixed, in that it no longer seemed to own that role's schema and I could modify the DDLAdmin roles membership for that user.

    I have a few databases exhibiting this behavior if anyone has any ideas or has further things to check. I am limited as to how much exact info I can post due to security considerations here.

    Thanks,

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

Viewing 0 posts

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