October 6, 2011 at 9:10 am
Using SQL 2005, I am trying to grant permission to delete records from a single table. I have run "Grant Delete on tbl_xyz to userxyz", but the user still gets "DELETE permission was denied on the object..." The user has one role on the database, DataReader. What the heck am I overlooking? I feel like this is a very stupid question, but I can't get it to work.
October 6, 2011 at 10:20 am
Did you check for DENY on that table? What does this return?
DECLARE @schema_name SYSNAME,
@table_name SYSNAME ;
SELECT @schema_name = 'schema_name',
@table_name = 'table_name' ;
-- schema permissions
SELECT s.name AS [name],
'SCHEMA' AS object_type,
dpr.name AS grantee,
dpr.type_desc AS principal_type,
dp.permission_name,
dp.state_desc
FROM sys.database_permissions dp
JOIN sys.schemas s ON dp.major_id = s.schema_id
JOIN sys.database_principals dpr ON dp.grantee_principal_id = dpr.principal_id
WHERE dp.class = 3
AND s.name = @schema_name
UNION
-- table permissions
SELECT s.name + '.' + o.name,
'TABLE',
dpr.name AS grantee,
dpr.type_desc,
dp.permission_name,
dp.state_desc
FROM sys.database_permissions dp
JOIN sys.objects o ON dp.major_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.database_principals dpr ON dp.grantee_principal_id = dpr.principal_id
WHERE dp.class = 1
AND s.name = @schema_name
AND o.name = @table_name ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 6, 2011 at 10:31 am
First do this:
1. create a new role
2. Grant delete to this role.
3. create a new user/login, add them to the new role
4. see if they can delete.
That tests the role. If it doesn't work, you might have some trigger or something else that prevents the delete.
Now, move your old user in the role. If they can't delete, you know you have a conflict of permissions. Likely you have some other conflicting permission.
Don't grant rights to a user. This user will quit, leave, move on, or someone else will need the rights. Use roles to keep things simpler for you, and the next DBA
October 6, 2011 at 10:39 am
Agreed on using Database Roles. Triggers are a place to look to cover all bases but with a message like "delete is denied on the object" this points to an object-level permission. Once you find the root cause and clear it up consider moving to Database Roles. Remember a DENY permission outranks a GRANT permission head-to-head in SQL Server (except when ownership chaining).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply