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.

thanks for any insight!
tracy

traceable1 (tracykc (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thank you!! That did it!

All of our users use Windows Authenticated accounts with rights
granted via groups, so I just created a SQL Server account for them to
use for this purpose.

Thanks again!!
Tracy


On Apr 3, 4:57 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote: