November 1, 2011 at 10:36 am
We have an outside process (run through our reporting system) that is supposed to trigger a job to import a file into a database table. On our 2000 server, we had much more lax security, and the user for that process had sysadmin rights. On the 2008R2 server that we're just starting to move to, we're locking security down and will NOT give those rights to that process' user.
We don't want to set this job up as a scheduled process, because it is dependent on a user downloading the file to be imported.
The job is triggered by a stored procedure as follows:
CREATE PROCEDURE [dbo].[RunSQLStmt]
@QueryString nvarchar(4000)
AS
EXEC (@QueryString)
SELECT 1
GO
The @QueryString sent through by the process was
Exec msdb.dbo.sp_start_job @job_name=(job name)
We got errors that the user didn't have rights to execute sp_start_job.
Some searching brought up this: http://msdn.microsoft.com/en-us/library/ms188283.aspx?ppud=4, but we don't want to grant SQLAgentOperatorRole to this one user because we don't want it to be able to run any jobs that we don't specifically grant it access to. So we searched some more.
Based on the response here: http://ask.sqlservercentral.com/questions/78375/what-is-best-way-to-allow-a-user-to-start-an-ad-hoc-job, I altered the stored procedure to do "WITH EXECUTE AS (sql agent user ID)" and got
Msg 916, Level 14, State 1, Procedure RunSQLStmt, Line 0
The server principal "(sql agent user ID)" is not able to access the database "(database name)" under the current security context.
Looking for info on that, I found http://www.sqlservercentral.com/Forums/Topic550938-146-1.aspx. I granted CONNECT to the SQL Agent user ID to that database...which then gave me the same error as before about the user not having the rights to execute sp_start_job.
The SQL Agent user ID has sysadmin rights on the database server.
About the only thing I can think of is that there's some permission that we don't have granted to the original process' database user to allow it impersonate the SQL Agent user ID, but I'm at a loss.
Thanks in advance for any help you can give me!
Jennifer Levy (@iffermonster)
November 1, 2011 at 3:49 pm
IINM, given that the privilege to run a job is a server-level permission, you may want something similar to what I decided to do for handling self-service logins. See this thread. Essentially, try and create a signed stored procedure mapped to a certificate that also exists in the master database and you'll need the permissions you need without granting user anything.
I would hope that also mean you don't need to have a sp that takes a query string and execute - even though it may come from a process you trust, the risk are too high, IMHO.
November 1, 2011 at 4:16 pm
Thanks very much! I'll give it a look. I've never done anything using certificates (no surprise since the document you've linked to notes that they weren't available prior to 2005), so I'll need to do some reading there for sure.
And we aren't going to be doing it quite that open-ended, no, but we've got several of these processes that we're moving over all at once, so that was the most generic way to show it. There will be some verification done prior to triggering the job that will limit it to only the processes we've approved.
Jennifer Levy (@iffermonster)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply