DB_Owner cannot insert

Question

DB_Owner cannot insert

jimmycjen

Default port

Points: 1457
More actions
September 1, 2011 at 10:05 am

#243133

I have a user who is a db_owner on a database. However, when he tries to insert to a table, it fails saying that he does not have permission to insert. It's not just this table, but in other tables too. I just couldn't figure out why. So, I removed his login and add it back but he still cannot insert. Can someone help me ? Thanks in advance.
We use SQL 2008 (Enterprise Edition) SP2 on Windows 2003 Server SP2, both 64-bit.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

is there a trigger on the table that is inserting into an audit table somewhere? maybe it's permissions on the Triggers Audit table that the user has no permissions for.

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!

jimmycjen Default port Points: 1457 More actions · Answer 2

Thanks for getting back to me. There is no triggers either at table level or database level. Are there anything else I should check ?

AppSup_dba SSCrazy Points: 2215 More actions · Answer 3

"deny" prevails over "grant" permissions... may be the user is added to some group with deny permissions...

Cheers

Lowell SSC Guru Points: 323518 More actions · Answer 4

well, it's one thing to *think* he ahs db_owner, and another one to check.

run these queries in the database in question:

select

userz.name,

userz.type_desc,

decripz.name as _member_of_this_role

from sys.database_principals userz

inner join sys.database_role_members rolez

on userz.principal_id = rolez.member_principal_id

inner join sys.database_principals decripz

on rolez.role_principal_id = decripz.principal_id

is he really in the db_owner role?

next, lets EXECUTE AS that specific user and see what his permissions are:

--Change into the user and see if he's normal

EXECUTE AS USER='mydomain\ClarkKent' --or a SQl role EXECUTE AS USER='Bob'

select * from fn_my_permissions(null,'SERVER')

select * from fn_my_permissions(null,'DATABASE')

REVERT;--change back into superman.

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!

jimmycjen Default port Points: 1457 More actions · Answer 5

Ankur, thanks for you suggestion and that was the problem - one pf the NT group has db_denywriter checked.

Thanks to all for helping me.

jimmycjen Default port Points: 1457 More actions · Answer 6

Lowell, thanks for the SQL code -- the first SELECT statement -- that was very helpful so that I don't have open each NT group manually to see what's in it.

jimmycjen Default port Points: 1457 More actions · Answer 7

Lowell,

When I ran the (first) SQL statement in a specific database, for some reason, it doesn't show all the users.

Lowell SSC Guru Points: 323518 More actions · Answer 8

jimmycjen (9/1/2011)
Lowell,
When I ran the (first) SQL statement in a specific database, for some reason, it doesn't show all the users.

that might be normal, if domain users get their access from a group.

for example, TheDomain\jimmycjen might not show up, but the group TheDomain\BusinessUsers DOES show up,a dn you are part of that Active Directory Group.

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!

jimmycjen Default port Points: 1457 More actions · Answer 9

I just found out that if no "role members" is selected in a group, even if the group exists, it won't show the group.

Thank you so much for your input.