August 2, 2018 at 12:51 pm
I have a developer who is a member of the db_owner database role in one of the databases he's responsible for. He's able to create tables, drop tables, but what he is unable to do is insert or delete data from tables. I've looked at the effective permissions for him on several tables, and they all are missing INSERT and DELETE. I've tried explicitly granting him said permissions, which has not resolved the issue.
The table in question has only one schema (dbo,) he is *NOT* the "Database Owner" (database properties->Files section)
I tried dropping his user from the database yesterday and re-adding (he does not / did not own any objects,) which did not resolve the issue.
Any ideas? Am I missing something basic and will feel like an id10t when it's pointed out to me?
August 2, 2018 at 1:12 pm
jasona.work - Thursday, August 2, 2018 12:51 PMI have a developer who is a member of the db_owner database role in one of the databases he's responsible for. He's able to create tables, drop tables, but what he is unable to do is insert or delete data from tables. I've looked at the effective permissions for him on several tables, and they all are missing INSERT and DELETE. I've tried explicitly granting him said permissions, which has not resolved the issue.The table in question has only one schema (dbo,) he is *NOT* the "Database Owner" (database properties->Files section)
I tried dropping his user from the database yesterday and re-adding (he does not / did not own any objects,) which did not resolve the issue.
Any ideas? Am I missing something basic and will feel like an id10t when it's pointed out to me?
Did you check for any denies at all in the database? Not for that developer, just any at all. For a particular user, those can get buried with group and role membership so I look for any and then work backwards to see if it affects that developer.
Sue
August 2, 2018 at 1:18 pm
Check for membership in any of the deny roles - there may be an explicit deny being applied for those tables.
Also - check the users default schema. If it is not dbo - then make sure the user is schema qualifying the code to insert and delete.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 3, 2018 at 5:50 am
Sue_H - Thursday, August 2, 2018 1:12 PMDid you check for any denies at all in the database? Not for that developer, just any at all. For a particular user, those can get buried with group and role membership so I look for any and then work backwards to see if it affects that developer.
Sue
Jeffrey Williams 3188 - Thursday, August 2, 2018 1:18 PMCheck for membership in any of the deny roles - there may be an explicit deny being applied for those tables.
Also - check the users default schema. If it is not dbo - then make sure the user is schema qualifying the code to insert and delete.
So, turns out I was going down the wrong rabbit hole. We do use AD groups for some access to the databases, and in the case of this production server there's a group with an explicit deny (db_denydatawriter) The group is used for some staff to generate reports...
Now, the dev in question has a "privileged" account they use to access the databases, which I didn't think would be in such a group...
I was wrong...
His privileged account is in that group. So now I get to shuffle the fix off to him and his supervisor as I can't add / remove people from AD groups.
Thank you both for the answers, they pushed me in the right direction on this, I was starting to tear out my hair...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply