April 10, 2012 at 12:15 am
Also posted at DBA Stackexchange.
I create two databases, a table in the second database and a stored procedure in the first database. The stored procedure cross-database accesses the table. I create a sql server login and I also map this login to a user in each of the databases. I give db_owner permission to the users. Here is the script that accomplishes it (I'm connected as a SQL sysadmin when running the script):
USE [master]
GO
CREATE DATABASE [TestDatabase1] ON PRIMARY
( NAME = N'TestDatabase1', FILENAME = N'd:\database\TestDatabase1.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestDatabase1_log', FILENAME = N'd:\database\TestDatabase1_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
CREATE DATABASE [TestDatabase2] ON PRIMARY
( NAME = N'TestDatabase2', FILENAME = N'd:\database\TestDatabase2.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestDatabase2_log', FILENAME = N'd:\database\TestDatabase2_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
USE [TestDatabase2]
GO
CREATE TABLE [dbo].[TestTable](
[Test] [int] NULL
) ON [PRIMARY]
GO
USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[TestDatabase1], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TestDatabase1]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
USE [TestDatabase1]
GO
ALTER USER [TestUser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [TestDatabase2]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
USE [TestDatabase2]
GO
ALTER USER [TestUser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [TestDatabase2]
GO
EXEC sp_addrolemember N'db_owner', N'TestUser'
GO
USE [TestDatabase1]
GO
EXEC sp_addrolemember N'db_owner', N'TestUser'
GO
Ones this is done, I connect to the server with SSMS under identity of the TestUser. I execute TestSp stored procedure in the SSMS and it succeeds.
Now I go ahead and create a job that executes the same stored procedure. I do it like this (I'm connected as a SQL sysadmin when running the script):
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TestJob',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestStep',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'SELECT TOP 1 * FROM TestDatabase2.dbo.TestTable',
@database_name=N'TestDatabase1',
@database_user_name=N'TestUser',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Once the job is created I run it from SSMS(I'm connected as a SQL sysadmin when doing this). The job fails with the following error:
Date 10/04/2012 3:26:31 p.m.
Log Job History (TestJob)
Step ID 1
Server obfuscated
Job Name TestJob
Step Name TestStep
Duration 00:00:00
Sql Severity 14
Sql Message ID 916
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: TestUser. The server principal "TestUser" is not able to access the database "TestDatabase2" under the current security context. [SQLSTATE 08004] (Error 916). The step failed.
And here is the clean up script to remove the database objects created by the scripts above:
USE [master]
GO
alter database TestDatabase1 set single_user with rollback immediate
GO
alter database TestDatabase1 set multi_user
GO
alter database TestDatabase2 set single_user with rollback immediate
GO
alter database TestDatabase2 set multi_user
GO
drop database TestDatabase1
GO
drop database TestDatabase2
GO
USE [msdb]
GO
declare @job_id uniqueidentifier
SELECT @job_id = job_id FROM msdb.dbo.sysjobs_view WHERE name = N'TestJob'
EXEC msdb.dbo.sp_delete_job @job_id=@job_id, @delete_unused_schedule=1
GO
DROP LOGIN [TestUser]
GO
Questions:
1. Why different results from a job and from SSMS?
2. How do I make the job work (instead of failing)?
UPDATE 1
Using advanced google-fu I was able to determine that one of the answers to Question 2 can be this:
ALTER DATABASE TestDatabase1 SET TRUSTWORTHY ON;
GO
RECONFIGURE WITH OVERRIDE;
GO
Question 1 still remains unanswered
April 10, 2012 at 2:34 am
Cross db ownership chaining.
In ssms, the login is mapped to users in both databases.
in the agent job, (or in a stored proc with ' execute as') the login no longer corresponds to the db user.
If you have two db's, and you define a user 'bob' in each one that isn't associated with a login, there is no connection between them. Setting trustworthy on makes sql server allow bob in db1 to access db2 as bob.
The sql agent job is equivalent to logging in as 'sa' and impersonating bob in db1. Once you do that, it's bob the db1 user, not sa the login that needs access to db2.
April 10, 2012 at 1:45 pm
Cross db ownership chaining.
In ssms, the login is mapped to users in both databases.
in the agent job, (or in a stored proc with ' execute as') the login no longer corresponds to the db user.
If you have two db's, and you define a user 'bob' in each one that isn't associated with a login, there is no connection between them. Setting trustworthy on makes sql server allow bob in db1 to access db2 as bob.
The sql agent job is equivalent to logging in as 'sa' and impersonating bob in db1. Once you do that, it's bob the db1 user, not sa the login that needs access to db2.
Thank you for this, this did help. I've done a little bit more research based on your answer and I think I get the picture now, although it's not entirely the same as in your answer. (However it may be just wording/perception thing.) It seems, that if a user name is specified for a step in a job, SQL Agent uses EXECUTE AS USER to run this step. According to http://msdn.microsoft.com/en-us/library/ms181362.aspx "While [this] context switch to the database user is active, any attempt to access resources outside of the database will cause the statement to fail." And this is exactly what I'm observing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply