db_owner's permission

Posted on 2004-08-10
Last Modified: 2008-03-10
A user is in db_owner role.  But she cannot delete a record in a table.  So I explicitly grant her the delete previldge in the table via enterprise manager.  Nothing changes.

Please advise why.

Question by:Jinghui Li
  • 5
  • 3
  • 2
  • +2
LVL 69

Assisted Solution

ScottPletcher earned 100 total points
ID: 11765928
A db_owner does not need explicit permission to delete a record in the table.  Something else must be causing the error, possibly:

1) Connected to a different server & db by mistake
2) Connected to a different db by mistake
3) Error in connection
4) Error in application/interface

Expert Comment

ID: 11765963
Perhaps the public role has an explicit deny.

Assisted Solution

andy232 earned 50 total points
ID: 11765967
good answers.  I would also check the table for triggers.  I would also run SQL Profiler and watch what happens.  You should try logging in as the user in Query Analzyer and running the delete.  You'll usually get a good error message there
LVL 42

Assisted Solution

EugeneZ earned 50 total points
ID: 11766034
also check relationship - FK PK of the table

Can you post  -error message

Author Comment

by:Jinghui Li
ID: 11766336
I ran the following query in Query Analyzer

DELETE  FROM  dbo.tbl1  WHERE     (Id = N'000000000')

Error Msg was

Server: Msg 229, Level 14, State 5, Line 1
DELETE permission denied on object 'tbl1', database 'xxx', owner 'dbo'.

I tried SQL Profiler but I did not know what the trace needs to record.  

Public role is not issue.  

What should I pay attention to about -FK PK

Accepted Solution

_ys_ earned 300 total points
ID: 11766645
> Public role is not issue.
Suggestion of public just an example. Are there any explicit denies at all?

> What should I pay attention to about -FK PK
You can't delete a PK whilst there's a FK to it. Basic database integrity. But the error message doesn't indicate this.
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.


Author Comment

by:Jinghui Li
ID: 11766773

I think you get it.  There is another role which has explicit denies on delete.  I have not tested it yet as I cannot log in as the person now.
LVL 69

Assisted Solution

ScottPletcher earned 100 total points
ID: 11767187
?  If you're 'sa' you can test their permissions without actually logging in as that user.

Author Comment

by:Jinghui Li
ID: 11767233
Yes I have the system roles - System Administrators.  I also find out the sa login if I really want to.  Do I have to use 'sa'?  And how should I test their permissions?


Author Comment

by:Jinghui Li
ID: 11767520
The person came back and it worked.

But I still love to get help on 'test their permission withou acutually loggin in as that user'.
LVL 69

Expert Comment

ID: 11767564
Go into Query Analyzer and issue this command:

SETUSER 'optionalDomain/userYouWantToSimulate'

Then any commands you issue will use the permissions of that user.

To reset back to your sa user, do this:

SETUSER  --[with no params]

Author Comment

by:Jinghui Li
ID: 11767568
I have set up another question for 'test their permission withou acutually loggin in as that user'.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now