Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Found a bug with Roles Expand / Collapse
Posted Thursday, November 29, 2012 1:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 23, 2016 11:28 AM
Points: 239, Visits: 997
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.


Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #1390819
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse