July 10, 2013 at 2:29 pm
I was faced this week with a problem. One job was behaving in a odd manner and we could not replicate the issue.
In the end the problem was that the job was accessing the wrong table, I just don't know why it was doing that.
So here is the scenario:
We have a job that runs against a database. That database has a table named DBO.SITE_MASTER and a synonym for the object RPT.SITE_MASTER that points to data on another database.
The job in question has a statement like (please note the absence of schema in the table name):
SELECT @ID=SITEID from SITE_MASTER where SITE_NAME=@SITE_NAME
After trying everything I could think of I noticed that whenever I executed that statement (with a sysadmin like account) I would access the DBO table. Whenever the SQL Agent job (running as "sa") executed that statement, it would access the synonym and return different results.
The fix was easy, and should be in place form the start (just fully qualify the table with the schema), but does anyone know WHY this is happening? I thought that SQL would check for the schema with the same user name, and if that was not available would always default to DBO.
Why is SQL in this case defaulting to a non-default schema, and only for the SQL Agent user?
Thanks in advance
July 10, 2013 at 2:32 pm
If SQL Agent is running as sa, then it will be running under the dbo user in the database and hence it's default schema is dbo.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2013 at 2:44 pm
That was what I thought too, but when the job executed (as "sa") it actually accessed the object pointed by the synonym, that is on the RPT schema, not the DBO schema.
July 10, 2013 at 3:21 pm
Any SQL Agent proxies in place?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2013 at 3:33 pm
Good question. There is one SQL Agent Proxy defined, but only for SSIS and OS steps.
The step this is failing on is a regular T-SQL step, calling one stored procedure that contains the code mentioned.
That user is also "sysadmin" on the instance, and there are no references to the Proxy for that job.
July 10, 2013 at 3:56 pm
Can you add a bit to that step that writes into some log table the database user name being used and the login name?
Suser_Sname() for login and USER_NAME() for database user name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2013 at 7:16 pm
Sure thing. I added this statement:
select Suser_Sname() as [login], USER_NAME() as
and the result was:
login user
------------------------------- ---------------------------------------------
domain\SAsqlAgentQA dbo
BTW, the login domain\SAsqlAgentQA is the login that runs the SQL Agent service, but not the SQL Server service.
July 11, 2013 at 1:16 am
Ok, so it's not actually running as 'sa'
Can you security properties for that login, whether it's mapped to any databases and if so what the default schemas are?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2013 at 2:12 am
Filipe (7/10/2013)
I thought that SQL would check for the schema with the same user name, and if that was not available would always default to DBO.
So this is not entirely correct. SQL Server will check the default schema for the current user. This default schema can be dbo, have the same name as the user or be something completely different.
It seems from the log information you added, that the database user was indeed dbo, and in that case the default schema should indeed be dbo. (And that can't be altered.)
My conclusion is that either at the point where the SELECT statement was executed, the default schema (and thus the user) was different, maybe because of an EXECUTE AS clause being in force. Or the job was reading from the right place, but the outcome was incorrect for some other reason.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 11, 2013 at 7:36 am
Gail - you are correct, and I assumed wrong. It's not running as "sa", I just thought that it was.
So I ran this SQL on that database (to get default schema and any permissions for the user):
select p.default_schema_name,dp.permission_name,rp.name as role_name
from sys.database_principals p join sys.database_permissions dp on p.principal_id=dp.grantee_principal_id
join sys.database_role_members r on p.principal_id=r.member_principal_id
join sys.database_principals rp on rp.principal_id=r.role_principal_id
where p.name='domain\sqlArcadia01'
to get the information you asked for, and the results were the following:
default_schema_name permission_name role_name
dbo CONNECT db_owner
Erland - I executed that select to get the user name one statement above the "offending" statement, so there's very little chance of changing users in between, and I made changes to the data in both tables to make sure I was able to identify which table I was hitting. I am able to replicate this behavior in a QA environment, where I am testing all this.
July 11, 2013 at 7:43 am
Now out of ideas...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2013 at 10:02 am
I echo Gail. From my experience of answering questions in forums, my gut feeling is that there is something you are not telling us. With access to the environment we might be able to answer, but as it that is not going to happen, we will have to leave that mystery unresolved.
At least you've got things working, so it is a not a practical problem for you.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 11, 2013 at 10:45 am
I appreciate the help anyway. If there is anything I'm not telling you is because I do not know it.
I'll keep trying to find an explanation or a way to duplicate the issue I can share, and if I find anything I will share in this post.
Thank you
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply