April 20, 2009 at 12:55 pm
I inherited a system where everyone was dbo.
Now that I have removed that we are taking it as a case by case situation, and coming up with a correct workaround.
I have a user that needs to delete or truncate the contents of one table without being a ddladmin or dbo on the whole database.
What are my options?
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
April 20, 2009 at 1:01 pm
If they only need to delete, you can grant delete against that table. If they need to truncate, you've got a lot larger problem as 2000 doesn't give you anything in the way of options. You can create a workaround where they have the ability to update a status table, a sql server agent checks the that status table periodically (say every 5 minutes), and if it sees the appropriate flag, it truncates the table.
K. Brian Kelley
@kbriankelley
April 20, 2009 at 1:12 pm
Ok, so I run the following:
GRANT DELETE ON dbo.table TO user
Then I give them Execute on the stored proc.
They get the error:
Server: Msg 3704, Level 16, State 1, Procedure usp_storedproc, Line 18
User does not have permission to perform this operation on table 'dbo.table'.
The drop is the only thing in the stored proc.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
April 20, 2009 at 1:18 pm
what if you execute a script like so, same error?
setuser 'USERNAME' -- Your user
go
delete [TABLENAME]
go
setuser
go
April 20, 2009 at 1:20 pm
"Only use SETUSER with SQL Server users. It is not supported with Windows users."
This is a Windows user.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
April 20, 2009 at 1:27 pm
SQL Server 2000 - If both objects are owned by dbo, then you don't need to grant explicit DELETE on the table. In fact, you shouldn't. You should let ownership chaining do its job.
With that said, the fact that you're getting an error indicates there's another issue. If you do an sp_help on that table, do other tables have foreign key references against that table? If so, do they have it with cascading deletes?
K. Brian Kelley
@kbriankelley
April 20, 2009 at 1:44 pm
sp_help on table gives:
ame Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ------------------------------------------------------
Table dbo user table 2009-04-13 09:31:05.697
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
SSN char no 9 yes no yes SQL_Latin1_General_CP1_CI_AS
DName varchar no 102 yes no no SQL_Latin1_General_CP1_CI_AS
DeathDate varchar no 8 yes no no SQL_Latin1_General_CP1_CI_AS
BirthDate varchar no 8 yes no no SQL_Latin1_General_CP1_CI_AS
ResStateName varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
ResCityName varchar no 52 yes no no SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
So nothing that sticks out to me there. All objects are dbo. I even tried to put her in a role w/o luck.
When I say DELETE I mean DROP the table. She can't Truncate or Drop. I just had her to DELETE FROM Table and it works.
Can I give her DROP/TRUNCATE? This is a small table so I'm not really worried but we'll have other times where I need to give someone the ability to drop a work table.
I may have been trying to do something I can't anyway.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
April 20, 2009 at 1:47 pm
With drop/truncate, you are going to have to do a work-around like K. Brian Kelley suggested. SQL 2K doesn't have granular enough permissions :ermm:
April 20, 2009 at 2:57 pm
ok. Thanks for the replies.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
April 20, 2009 at 3:25 pm
I am just curious, but why would someone need to drop a work-table on a regular basis?
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply