|
|
Posted by traceable1 on 04/04/07 15:07
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...@sommarskog.se> wrote:
> traceable1 (trac...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|