March 12, 2008 at 12:36 pm
I have a user (UserDev)who has dbo rights on a development box database (sql 2005). After creating a table tblXYZ using CREATE TABLE [dbo].[tblXYZ]..., a stored proc was created using CREATE PROCEDURE dbo.procTruncateXYZ AS TRUNCATE TABLE..., followed by a GRANT EXECUTE ON [dbo].[procTruncateXYZ] TO [sqlsvcacctABC].
When these objects were scripted in a QA region by dba with the above commands included where the user/developer UserDev has DB_DATAREADER rights, an attempt was made by UserDev to execute procedure procTruncateXYZ, with an expectation that the called procedure would still be able to perform the table truncation.
Question 1: Should UserDev be able to do this successfully?
Question 2: The above objects were included in a package, which was scheduled as a job, but the job fails also during what appears to be truncation step?
Are we overlooking a step or issuing grants incorrectly?
Thanks for any info.
March 13, 2008 at 9:50 am
UserDev has dbo right. It should be able to do it.
While running DTS package, what account did you use?
March 13, 2008 at 10:10 am
For Question 1, UserDev is dbo in development, but only has DB_DATAREADER and DB_DATAWRITER in QA server. Thats why I was wondering if the stored procedure needed to some type of "run as" grant applied, so that when UserDev is logged into QA, he still can execute the stored procedure, which I was hoping would enable it to perform a truncate when it "ran as" dbo on QA.
For Question 2, the DTS package is executed by the service account on that server. Does it need grants also?
March 17, 2008 at 9:53 pm
What do you mean the service account? Whether or not an account has permission to complete a DTS depends whether or not this account has authority to manage all related objects.
March 17, 2008 at 10:36 pm
Truncate requires at least ALTER TABLE permissions on that table. You can either grant the appropriate rights, use the EXECUTE AS clause in the procedure, or use a signed procedure (search on procedure signing). The second and third options are probably best if this proc is going to be called by more than this one user.
March 19, 2008 at 10:17 am
Further to Matt's point above (see http://msdn2.microsoft.com/en-us/library/ms190384.aspx):
The following stored procedure will need to be created by the table owner and the user will need to be assigned EXECUTE permissions on the sproc:
CREATE PROCEDURE TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;
Quoting from http://msdn2.microsoft.com/en-us/library/ms190384.aspx):
Specifying an execution context for a module can be very useful in defining custom permission sets. For example, some actions such as TRUNCATE TABLE, do not have grantable permissions. To execute TRUNCATE TABLE, the user must have ALTER permissions on the specified table. Granting a user ALTER permissions on a table may not be ideal because the user will effectively have permissions well beyond the ability to truncate a table.
By incorporating the TRUNCATE TABLE statement within a module and specifying that module execute as a user that has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the module.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 19, 2008 at 11:29 am
Thank you to all that replied...I think the execution context suggestion is probably the way to go in this situation. We will give it a try.
March 19, 2008 at 12:02 pm
- IMO the "execute as .." is the preferable path.
If that cannot be done, maybe the (log)overhead of a simple delete from tablexyz will just do to avoid overauthorized users.
- also keep in mind you cannot truncate a table that is parent table for others. (DRI)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply