Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a SQL Agent job that is configured to run as user "dbo". This job gets a list of databases defined in sys.databases that match a naming scheme. The job then performs some dynamic SQL to cleanup some old transaction tables. The job starts against tempdb.

The agent job runs fine on my own development database (SQL Server 2008 R2 Standard Edition 10.50.2500.0). The job correctly gets the list of databases. However, on a customer system (SQL Server 2008 R2 Standard Edition 64-bit 10.50.1600.1), the job does not find any matching databases despite them existing.

On the customer system, a simple select * from sys.databases run against tempdb in a query window returns all 10 databases in the system.

Using the profiler, I saw SQL Agent call EXECUTE AS USER = N'dbo' WITH NO REVERT when running this job. I duplicated the issue using the following SQL:

use tempdb

-- results: tempdb, <my SQL user>, dbo
select
    db_name() as [Current Database],
    suser_name() as [Current Context],
    user_name() as [Current User]

-- results: tempdb, 10 (which I expect)
select
    db_name() as [Current Database],
    count(*)
from sys.databases

execute as user = N'dbo'

-- results: tempdb, sa, dbo
select
    db_name() as [Current Database],
    suser_name() as [Current Context],
    user_name() as [Current User]

-- results: tempdb, 2 (?!?)
select
    db_name() as [Current Database],
    count(*)
from sys.databases

revert

If I change 'use tempdb' to 'use master', the results are the expected 10 databases for each query.

Why would sys.databases return different results when run as the user dbo from the tempdb database? And why does it return all databases with use master as the dbo user?

More information - If I run this script in SQL Server Management Studio without including the revert at the bottom, my list of databases shrinks to the same two that this query returns from sys.databases.

share|improve this question

1 Answer 1

up vote 7 down vote accepted

This appears to be a matter of access rights and the specific user in the database. The first thing to understand is that sys.databases in any database is simply a view that exposes parts of the system table that lives in resourcedb. Anytime you are querying that in any database, you're hitting the same table but through a different view.

The second thing to understand is each database has a dbo user. When you use the execute as user = N'dbo', that means you're dbo in the context of that specific database. In your specific example, the dbo user in master and the dbo user in tempdb are two different security principals.

What you're seeing is simply a matter of what each principal is allowed to see. dbo users, being the owner login of each specific database, only have rights to see the following databases:

  • master (in order to look up control information)
  • tempdb (in order to create temporary objects when necessary)
  • The database they own

Note that the dbo user in master, since that database needs to see and supply control information for all databases, has rights to view all databases on the server.

share|improve this answer
    
Thank you. Would you recommend not running this as a specific user? If I leave the user empty when creating the job, the job runs as NT AUTHORITY\SYSTEM, and it runs correctly. I'm not sure what security to use for a SQL Agent job that works on multiple databases. –  Paul Williams Feb 6 '13 at 19:23
    
Typically, I set job owner as sa and have it execute as that. This means you'll need to sure you have vetted the code running under that context, but as long as you've got good control over that as well as who can create/execute SQL jobs you should be covered. –  Mike Fal Feb 6 '13 at 19:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.