Posted by Erland Sommarskog on 04/03/07 21:57

traceable1 (tracykc@gmail.com) writes:
> I have users that need to schedule jobs (SQL 2000, SP4). They can
> create jobs, but can only see the core databases when they go to enter
> a step (master,msdb,Northwind,tempdb, pubs).
>
> The users have db_reader and db_writer on other databases - why won't
> they come up? I can't give the users dbo or sysadmin.

I traced the create-job dialog, and I found one call of these per database
on the server:

DECLARE @UserName NVARCHAR(128)
EXECUTE msdb.dbo.sp_get_jobstep_db_username N'sqlbpa',
N'sommar', @UserName OUTPUT
SELECT @UserName

I then used sp_helptext to look at the code for sp_get_jobstep_db_username,
and it appears that for Windows logins, the procedure accepts this
without checking. However, I found when tracing on a server where I
connected with Windows Authentication, that Agent passes the empty string
for the login name.

Anyway, I would suspect that the problem is that there is a mismatch
between the sid for the database user and the SID for the login. Possibly
because the databases have been restored from another server.
sp_change_users_login can sort this out.

Exactly how these users connect? SQL/Windows authentication? If the
latter, are the granted access individually, or through an NT group?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]