April 22, 2011 at 11:26 am
I need to grant a system user that runs job and give it permission of trunacte a table, also insert records to the table.
What the best way to grant permissions, we don't want to give it too big privillege, like dbowner.
I tried grant alter table, it didn't work on SQL 2000.
So I give the user ddladmin for the database, shall I also give it grant insert on the table?
I know there may be better to put them into stored procedure, and grant execute on the sproc, this is the best approach, correct?
But in this case the sproc are not availabe, so what is the best way of doing this?
Thanks
April 22, 2011 at 11:52 am
does it have to be the truncate table or will delete work for this as well?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 22, 2011 at 8:01 pm
Yes, it has to be truncate table, and it will not do delete records from the table, but will insert records into the table.
April 23, 2011 at 7:49 am
Just checking because, though slower and more logged, the delete can have specific permissions granted to it unlike the truncate. In either case you would be deleting the records in the table in prep to do your insert.
Your best option is to write a stored procedure to do it and grant exec on the proc.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply