cannot delete with DELETE permission?

Hello,
One of our users said he was not able to delete records in a table. When I
looked, he did not have DELETE on that table. Then I checked DELETE for his
account. But he still cannot delete any records. I don't think there is
anything more I need to do to make the permission change take effect.
Correct me if I'm wrong. So what I'm missing here?
Thanks in advance,
BingFor some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
WHERE clause (find the rows).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Oh, sorry, I forgot to mention I also granted this user other permissions
INSERT, SELECT and UPDATE along with DELETE at the same time. He uses a
domain account accessing SQL 2000 from Access. Anything else could prevent
him from deleting records?
Thanks for the good clue you gave. I did not think of that.
Bing
"Tibor Karaszi" wrote:
> For some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
> WHERE clause (find the rows).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> > Hello,
> >
> > One of our users said he was not able to delete records in a table. When I
> > looked, he did not have DELETE on that table. Then I checked DELETE for his
> > account. But he still cannot delete any records. I don't think there is
> > anything more I need to do to make the permission change take effect.
> > Correct me if I'm wrong. So what I'm missing here?
> >
> > Thanks in advance,
> >
> > Bing
>|||Does this table have a delete trigger that does insert/update on
another table (where the user does not have permissions)?
Use sp_helptrigger to find out
http://sqlservercode.blogspot.com/|||TableDeleteTrigger is 0. So I assume the table has no connection with other
tables, right?
Bing
"SQL" wrote:
> Does this table have a delete trigger that does insert/update on
> another table (where the user does not have permissions)?
> Use sp_helptrigger to find out
> http://sqlservercode.blogspot.com/
>|||Check to ensure the user's login is correctly mapped to the desired database
user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
mapping is incorrect following a database restore/attach. If that is you
problem, you can correct using sp_change_users_login. See the Books Online
for usage details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When
> I
> looked, he did not have DELETE on that table. Then I checked DELETE for
> his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Thanks much for the information. sp_helplogins shows:
LoginName SID DefDBName DefLangName AUser ARemote
DEPT\jsmith <omitted> Staff us_english yes no
LoginName DBName UserName UserOrAlias
DEPT\jsmith Staff db_owner MemberOf
DEPT\jsmith Staff Staffadmins MemberOf
DEPT\jsmith Staff dept\jsmith User
(Note, sp_helplogins shows 'dept' in lower case in the above last line. Not
my typo.)
We did not do restore/attach on this database lately. From my
understanding, user level permissions should override group or role level
permissions, yes? Staffadmins is defined as a role in Roles of the Staff
database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
EXEC, DRI) were not checked for the role Staffadmins. Does this matter? I
think it should not because user DEPT\jsmith have been granted all the
permissions.
Biing
"Dan Guzman" wrote:
> Check to ensure the user's login is correctly mapped to the desired database
> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
> mapping is incorrect following a database restore/attach. If that is you
> problem, you can correct using sp_change_users_login. See the Books Online
> for usage details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> > Hello,
> >
> > One of our users said he was not able to delete records in a table. When
> > I
> > looked, he did not have DELETE on that table. Then I checked DELETE for
> > his
> > account. But he still cannot delete any records. I don't think there is
> > anything more I need to do to make the permission change take effect.
> > Correct me if I'm wrong. So what I'm missing here?
> >
> > Thanks in advance,
> >
> > Bing
>
>|||bing skrev:
> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
Is there a DENY somewhere, perhaps? Possibly for a role this user is
member of.
/impslayer, aka Birger Johansson|||Permissions are cumulative and include those directly assigned plus role
membership. When conflicting permissions exist, DENY takes precedence over
GRANT.
> DEPT\jsmith Staff db_owner MemberOf
db_owner role is a very powerful role and is not normally used for normal
users because you can't limit database object permissions for db_owner role
members. Consequently, even if you denied permissions to this user, the
user should still have full object permissions.
You mentioned in your original message that the user could not DELETE from
the table. Are you certain this is permission related? What is the exact
error?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:4C564F8E-8F72-4DB4-9A54-06CA72CA3D28@.microsoft.com...
> Thanks much for the information. sp_helplogins shows:
> LoginName SID DefDBName DefLangName AUser ARemote
> DEPT\jsmith <omitted> Staff us_english yes
> no
> LoginName DBName UserName UserOrAlias
> DEPT\jsmith Staff db_owner MemberOf
> DEPT\jsmith Staff Staffadmins MemberOf
> DEPT\jsmith Staff dept\jsmith User
> (Note, sp_helplogins shows 'dept' in lower case in the above last line.
> Not
> my typo.)
> We did not do restore/attach on this database lately. From my
> understanding, user level permissions should override group or role level
> permissions, yes? Staffadmins is defined as a role in Roles of the Staff
> database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
> EXEC, DRI) were not checked for the role Staffadmins. Does this matter?
> I
> think it should not because user DEPT\jsmith have been granted all the
> permissions.
> Biing
> "Dan Guzman" wrote:
>> Check to ensure the user's login is correctly mapped to the desired
>> database
>> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
>> mapping is incorrect following a database restore/attach. If that is you
>> problem, you can correct using sp_change_users_login. See the Books
>> Online
>> for usage details.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "bing" <bing@.discussions.microsoft.com> wrote in message
>> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>> > Hello,
>> >
>> > One of our users said he was not able to delete records in a table.
>> > When
>> > I
>> > looked, he did not have DELETE on that table. Then I checked DELETE
>> > for
>> > his
>> > account. But he still cannot delete any records. I don't think there
>> > is
>> > anything more I need to do to make the permission change take effect.
>> > Correct me if I'm wrong. So what I'm missing here?
>> >
>> > Thanks in advance,
>> >
>> > Bing
>>|||"impslayer" wrote:
> bing skrev:
> > Hello,
> >
> > One of our users said he was not able to delete records in a table. When I
> > looked, he did not have DELETE on that table. Then I checked DELETE for his
> > account. But he still cannot delete any records. I don't think there is
> > anything more I need to do to make the permission change take effect.
> > Correct me if I'm wrong. So what I'm missing here?
> >
> > Thanks in advance,
> >
> > Bing
> Is there a DENY somewhere, perhaps? Possibly for a role this user is
> member of.
> /impslayer, aka Birger Johansson
>
Thanks all who replied.
Yeah, as my sp_helplogins shows, this user is member of db_owner. He
actually is not a regular user. He is indeed the database owner. So I
really don't understand why he cannot delete. This user did not tell me the
exact error message he got if there was any. I need to check back with him
on that.
I don't see any explicit DENY related to this user. These are what've
checked:
1. Security->Login
Permit Database User
X Staff DEPT\jsmith
2. Staff -> Users
Database role membership->Permit in Database Role
public
db_owner
staffadmins
DEPT\jsmith permissions:
ME538 (SELECT, INSERT, UPDATE, DELETE)
3. Staff -> Roles
Staffadmins->Permissions:
ME538 is the only table that does not have SELECT, INSERT, UPDATE and
DELETE explicitly checked. And this is the table the user needs to delete
from but he cannot.
What am I missing?
Bing|||bing skrev:
> This user did not tell me the
> exact error message he got if there was any. I need to check back with him
> on that.
Ah, here's the problem, I guess :)
/impslayer