January 27, 2009 at 1:18 pm
I created a stored proc where it rebuilds indexes and i would like this proc to run by developers who are not sysadmin or db_owner, they just have read/write/ddl_admin previlages on the server.
My proc uses alter index,rebuild,reorganize commands. when this proc was hooked to a heavy data load job as a final step it failed to run this proc though i gave EXEC permissions on that proc.
How can i make developers to run this proc as part of their sql agent job?
January 27, 2009 at 1:32 pm
Did it not run because of a permissions issue or did it not run because it collided with the data load job? What was the exact error? Also, can you post the code for the proc so we can see exactly what it's doing?
K. Brian Kelley
@kbriankelley
January 27, 2009 at 1:48 pm
Its the same same cod which you can see here
http://technet.microsoft.com/en-us/library/bb838727.aspx
Users got permissions error.
January 27, 2009 at 1:54 pm
Here is the exact error mesg.
Executed as user: StatePA\PZarah. Cannot find the object "dbo.Rev2001" because it does not exist or you do not have permissions. [SQLSTATE 42000] (Error 1088). The step failed.
It was the same error on all the databases. when i checked that user has permissions to that table and also that was the first table hitting the rebuild job.
January 27, 2009 at 2:21 pm
Mike Levan (1/27/2009)
Here is the exact error mesg.Executed as user: StatePA\PZarah. Cannot find the object "dbo.Rev2001" because it does not exist or you do not have permissions. [SQLSTATE 42000] (Error 1088). The step failed.
It was the same error on all the databases. when i checked that user has permissions to that table and also that was the first table hitting the rebuild job.
You mentioned something about scheduling this sp under the sql server agent account, does this account have the priviliges to execute this commands?
Did you just give permissions on the sp or also on the tables?
January 27, 2009 at 2:33 pm
Yes, I just gave permission to to those tables and EXEC to the proc. Can you pls let me know what permissions they need to run this rebuild job.
January 27, 2009 at 2:40 pm
Is this user StatePA\PZarah the developer account or the SQL Server Agent service account?
K. Brian Kelley
@kbriankelley
January 27, 2009 at 3:52 pm
That User is windows account associated with the windows group. I gave access to the group so that they can exec store proc.
January 27, 2009 at 4:11 pm
Then you want to use the EXECUTE AS clause in the CREATE PROC definition to impersonate, say, dbo. That will give the rights necessary. When they execute the stored procedure, any code within the stored procedure or any batches started by the stored procedure (such as the dynamic SQL) will execute under the impersonated user.
K. Brian Kelley
@kbriankelley
January 28, 2009 at 8:17 am
Brain
could you let me know how that can be modified in this query
January 28, 2009 at 2:26 pm
Brain
I have a question on your post. If EXECUTE AS can be used to run a store proc where a developer dont have rights to do, then doies it mean he can run any store proc by adding EXECUTE AS clause?
January 28, 2009 at 8:35 pm
The EXECUTE AS is part of the stored procedure definition itself. If you look at the Books Online write-up for CREATE PROCEDURE, you'll see in the syntax specification and in an example (see example H):
Books On-line (SQL Server 2005): CREATE PROCEDURE
This was a new feature in SQL Server 2005. It will not apply to previous versions of SQL Server.
K. Brian Kelley
@kbriankelley
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply