November 14, 2011 at 2:44 pm
On our sql server 2008 R2 server ( SP1 is not applied yet ), we have database 'Adomain' with domain account as database owner. We have database Bsa with 'sa' as database owner. Both the accounts are sysadmin on the server and enabled.
We have one job executes procedure on database Bsa and tries to read information from database ADomain.
The job failes with the following message:
Executed as user: dbo. The server principal "sa" is not able to access the database ADomain under the current security context. [SQLSTATE 08004] (Error 916). The step failed.
Job is owned by the domain account with sysadmin rights.
Why 'sa' account is not able to access the database?
Also, another discussion we have going on is whether it is a good idea to have 'sa' as a database owner.
Any help is appreciated.
Thanks,Aashini
November 14, 2011 at 4:39 pm
I like to have the Database Owner set as SA.
As for the access problem, is your server setup in mixed mode authentication?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 15, 2011 at 12:33 am
We have one job executes procedure on database Bsa and tries to read information from database ADomain.
The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. By default, this setting is OFF, but can be set to ON by using the ALTER DATABASE statement.
For example,
ALTER DATABASE AdventureWorks2008R2 SET TRUSTWORTHY
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply