Re: sp_start_job, run by normal users?
- From: INTP56 <INTP56@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 9 Jun 2010 11:12:10 -0700
OK, I've looked over your code again and made some changes and have gotten
further along.
I was able to create a database, add a user to a role, and get it to execute
sp_start_job.
However, I'm still having problems getting it to complete. The last two
people who tried the code got the following error:
ERROR_PROCEDURE(sp_verify_job_identifiers)
ERROR_LINE(67)
ERROR_NUMBER(14262)
ERROR_SEVERITY(16)
ERROR_STATE(1)
ERROR_MESSAGE(The specified @job_name ('TestStartJob') does not exist.)
sp_verify_job_identifiers is called by sp_start_job, and I went into that
code and found this code section where it's failing:
.....
-- The name is not ambiguous, so get the corresponding job_id (if the
job exists)
SELECT @job_id = job_id,@owner_sid = owner_sid
FROM msdb.dbo.sysjobs_view
WHERE (name = @job_name)
-- the view would take care of all the permissions issues.
IF (@job_id IS NULL)
BEGIN
RAISERROR(14262, -1, -1, '@job_name', @job_name)
RETURN(1) -- Failure
END
....
Apparently the user I tried from my last post had enough rights for that
view.
He failed in sp_start_job at the test following the call to
sp_verify_job_identifiers:
.....
EXECUTE @retval = sp_verify_job_identifiers '@job_name',
'@job_id',
@job_name OUTPUT,
@job_id OUTPUT,
@owner_sid = @job_owner_sid
OUTPUT
IF (@retval <> 0)
RETURN(1) -- Failure
-- Check permissions beyond what's checked by the sysjobs_view
-- SQLAgentReader role can see all jobs but
-- cannot start/stop jobs they do not own
IF (@job_owner_sid <> SUSER_SID()
AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0)
AND (ISNULL(IS_MEMBER(N'SQLAgentOperatorRole'), 0) = 0))
BEGIN
RAISERROR(14393, -1, -1);
RETURN(1) -- Failure
END
.....
It seems like I'm going to have to add some sort of EXECUTE AS clause to
switch my security context that will get carried to the various objects used
by sp_start_job.
I created an Excel WB that has a macro to call dbo.StartAgentJob. I've sent
the WB to various coworkers and have them run it under their account. If I
log into their machine as me it runs as expected without errors, so I'm
confident the only difference is security context.
Bob
P.S. Here is my code so far:
-- Script to test running sp_start_job from a user database.
USE master;
--***********************************************
--Delete left over certificate files.
--Normally, you are not allowed to execute xp_cmdshell.
--You could just go to c:\temp on the server
--and delete them yourself.
--
--However, for sake of just being able to hit F5 and
--having the script do everything,
--I turn it on, do my EXEC, and turn it back off.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1;
GO
RECONFIGURE;
GO
EXEC master..xp_cmdshell 'DEL C:\temp\msdb*'
GO
EXEC sp_configure 'xp_cmdshell',0;
GO
RECONFIGURE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO
--
--
--***********************************************
--Remove previous objects from msdb.
USE msdb;
GO
IF EXISTS (SELECT * FROM sys.database_principals
WHERE name = N'msdbAuthenticator')
DROP USER msdbAuthenticator;
GO
DROP CERTIFICATE msdbCertificate;
GO
--
--***********************************************
--Drop target database
USE master;
GO
DROP DATABASE dbPermissionTest;
GO
--
--At this point:
-- certificate and private key files are deleted.
-- msdb should be as it was.
-- The target database (dbPermissionTest) is gone.
--
--
--
--*********************************************
--Create target DB and supporting objects.
CREATE DATABASE dbPermissionTest;
GO
USE dbPermissionTest;
GO
CREATE TABLE dbo.MessageTable
(RowNum
INT
IDENTITY(1,1)
NOT NULL
,CONSTRAINT PK_MessageTable
PRIMARY KEY
CLUSTERED
(RowNum DESC)
,RecordCreated
DATETIME
CONSTRAINT DF_RecordCreated#MessageTable
DEFAULT (GETDATE())
NOT NULL
,PrimaryAccount
NVARCHAR(128)
CONSTRAINT DF_PrimaryAccount#MessageTable
DEFAULT (ORIGINAL_LOGIN())
NOT NULL
,UserAccount
NVARCHAR(128)
CONSTRAINT DF_UserAccount#MessageTable
DEFAULT (USER_NAME())
NOT NULL
,ProcedureName
NVARCHAR(128)
NULL
,Message
NVARCHAR(128)
NULL
);
GO
INSERT INTO dbo.MessageTable(ProcedureName,Message)
VALUES (N'CREATE TABLE dbo.MessageTable',N'Initial Insert');
GO
CREATE PROCEDURE dbo.CollectErrors
(@ErrorProcedure NVARCHAR(128) = NULL OUTPUT
,@ErrorLine INT = NULL OUTPUT
,@ErrorNumber INT = NULL OUTPUT
,@ErrorSeverity INT = NULL OUTPUT
,@ErrorState INT = NULL OUTPUT
,@ErrorMessage NVARCHAR(4000) = NULL OUTPUT
,@ErrorString NVARCHAR(4000) = NULL OUTPUT
,@PrintErrorValues BIT = 0
) AS SET NOCOUNT ON;
--This is the PROC called by the job.
DECLARE @strEP NVARCHAR(128),@strEL NVARCHAR(10),@strEN NVARCHAR(10);
DECLARE @strEV NVARCHAR(10) ,@strES NVARCHAR(10),@strEM NVARCHAR(4000);
BEGIN
SELECT
@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorLine = ERROR_LINE()
,@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorMessage = ERROR_MESSAGE()
;
SELECT
@strEP = ISNULL(@ErrorProcedure,N'<NULL>')
,@strEL = ISNULL(CAST(@ErrorLine AS NVARCHAR),N'<NULL>')
,@strEN = ISNULL(CAST(@ErrorNumber AS NVARCHAR),N'<NULL>')
,@strEV = ISNULL(CAST(@ErrorSeverity AS NVARCHAR),N'<NULL>')
,@strES = ISNULL(CAST(@ErrorState AS NVARCHAR),N'<NULL>')
,@strEM = ISNULL(@ErrorMessage,N'<NULL>')
;
SET @ErrorString =
N'ERROR_PROCEDURE('+ @strEP + N')'
+ NCHAR(13) + NCHAR(10)
+ N'ERROR_LINE(' + @strEL + N') '
+ N'ERROR_NUMBER(' + @strEN + N') '
+ N'ERROR_SEVERITY(' + @strEV + N') '
+ N'ERROR_STATE(' + @strES + N') '
+ NCHAR(13) + NCHAR(10)
+ N'ERROR_MESSAGE(' + @strEM + N') '
;
IF @PrintErrorValues = 1
BEGIN
PRINT N'ERROR_PROCEDURE('+ @strEP + N')';
PRINT N'ERROR_LINE(' + @strEL + N')';
PRINT N'ERROR_NUMBER(' + @strEN + N')';
PRINT N'ERROR_SEVERITY(' + @strEV + N')';
PRINT N'ERROR_STATE(' + @strES + N')';
PRINT N'ERROR_MESSAGE(' + @strEM + N')';
END
;
END;
GO
CREATE PROCEDURE dbo.WriteMessage
(@Message NVARCHAR(128) = '<None>'
) AS SET NOCOUNT ON;
--This is the PROC called by the job.
BEGIN
BEGIN TRY
INSERT INTO dbo.MessageTable(ProcedureName,Message)
VALUES (N'dbo.WriteMessage',@Message);
END TRY
BEGIN CATCH
EXEC dbo.CollectErrors @PrintErrorValues = 1;
END CATCH;
END;
GO
EXEC dbo.WriteMessage @Message = 'Test EXEC';
GO
CREATE PROCEDURE dbo.StartAgentJob
AS SET NOCOUNT ON;
--This is the PROC called within user security context.
BEGIN
BEGIN TRY
INSERT INTO dbo.MessageTable(ProcedureName,Message)
VALUES (N'dbo.StartAgentJob','Before start');
END TRY
BEGIN CATCH
EXEC dbo.CollectErrors @PrintErrorValues = 1;
END CATCH;
BEGIN TRY
EXEC msdb.dbo.sp_start_job N'TestStartJob';
END TRY
BEGIN CATCH
EXEC dbo.CollectErrors @PrintErrorValues = 1;
END CATCH;
BEGIN TRY
INSERT INTO dbo.MessageTable(ProcedureName,Message)
VALUES (N'dbo.StartAgentJob','After start');
END TRY
BEGIN CATCH
EXEC dbo.CollectErrors @PrintErrorValues = 1;
END CATCH;
END;
GO
EXEC dbo.StartAgentJob;
GO
--NOTE: This may not show the last write to the table.
--That's because it takes some time for the job to execute.
SELECT * FROM dbo.MessageTable;
GO
-- At this point, the target database and objects have
-- been created. StartAgentJob runs successfully because
-- I'm running this script under my LocalAdmin account.
--
-- What follows are the extra things to do to support
-- regular users being able to start a job.
--
--
--***********************************************
--Create certificate and user in msdb,
--Backup certificate and grant EXECUTE permission
USE msdb;
GO
CREATE CERTIFICATE msdbCertificate
ENCRYPTION BY PASSWORD = 'AWeakPassw0rd'
WITH
SUBJECT = 'Cross DB Certificate Test'
,START_DATE = '6/1/2010'
,EXPIRY_DATE = '6/30/2010'
;
GO
BACKUP CERTIFICATE msdbCertificate
TO FILE = 'c:\temp\msdb_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'c:\temp\msdb_PrivateKey.pvk'
,ENCRYPTION BY PASSWORD = 'AWeakerPassw0rd'
,DECRYPTION BY PASSWORD = 'AWeakPassw0rd'
);
GO
CREATE USER msdbAuthenticator FROM CERTIFICATE msdbCertificate;
GO
GRANT EXECUTE ON msdb.dbo.sp_start_job TO msdbAuthenticator;
GO
--
--
--
--***********************************************
--Create certificate from file and user in dbPermissionTest,
--Backup certificate and grant EXECUTE permission
USE dbPermissionTest;
GO
CREATE CERTIFICATE msdbCrossDBCertificate
FROM FILE = 'c:\temp\msdb_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'c:\temp\msdb_PrivateKey.pvk'
,ENCRYPTION BY PASSWORD = 'An0therWeakPassw0rd'
,DECRYPTION BY PASSWORD = 'AWeakerPassw0rd'
);
GO
ADD SIGNATURE TO OBJECT::StartAgentJob
BY CERTIFICATE msdbCrossDBCertificate
WITH PASSWORD = 'An0therWeakPassw0rd';
GO
CREATE ROLE RunJobRole;
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.MessageTable TO RunJobRole;
GO
GRANT EXECUTE ON dbo.StartAgentJob TO RunJobRole;
GO
--Now add users to the database and put them in RunJobRole.
--****** REMOVED *********************************************
--****** Statements of the form:
--****** CREATE USER [Domain\User] FROM LOGIN [Domain\User];
--****** EXEC sp_addrolemember 'RunJobRole','Domain\User';
--
--At this point, I can switch to Excel (PermissionTest.xls)
--where I have a macro that calls this procedure using MDAC 2.8.
--NOTE: This is the most likely the scenario I have to support:
-- Someone logged into their machine
-- using a connection string specifying SSPI
-- calling the procedure via an ADO command
--
--However, both my accounts can see the SQL Server Agent from
--the object explorer in SSMS, so this is not a real test.
--
.
- Follow-Ups:
- Re: sp_start_job, run by normal users?
- From: Erland Sommarskog
- Re: sp_start_job, run by normal users?
- References:
- sp_start_job, run by normal users?
- From: INTP56
- Re: sp_start_job, run by normal users?
- From: Erland Sommarskog
- sp_start_job, run by normal users?
- Prev by Date: migrating to sql2008...switch to TDE?
- Next by Date: Re: migrating to sql2008...switch to TDE?
- Previous by thread: Re: sp_start_job, run by normal users?
- Next by thread: Re: sp_start_job, run by normal users?
- Index(es):
Relevant Pages
|
