SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Found a bug with Roles

Found a bug with Roles

SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2981 Visits: 1077
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.


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum