Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2002
    Location
    Hoffman Estates, IL
    Posts
    14

    Unanswered: Database with dash job run as dbo

    I have written a script that needs to run as dbo and is triggered by a 9002 alert and it runs fine on about 500 databases, however I can not get it to run on databases that have a dash in their name (Nor if they have a space).

    I am running 8.00.534 SQL Server 2000 SP2 November 30th, 2001 Standard Edition.

    To reproduce the problem:

    #1 Create a database named "test-dash"
    #2 Create a job "testjob" with owner sa
    #3 in enterpise manager under job steps click "new"
    #4 General tab:
    Step Name: testing
    Type: TSQL
    Database: test-dash
    Comand: select 'hello world'
    #5 Advance tab:
    Run as user: dbo
    #6 click ok and click ok

    It returns:
    Error 170: Line 3: Incorrect syntax near '-', the job was not saved,

    Apparently it is dying on line 148 of msdb.dbo.sp_verify_jobstep
    EXECUTE(N'DECLARE @ret INT
    SELECT @ret = COUNT(*)
    FROM ' + @database_name_temp + N'.dbo.sysusers
    WHERE (name = N''' + @database_user_name_temp + N''')
    HAVING (COUNT(*) > 0)')

    Which should generate:
    SELECT COUNT(*)
    FROM [test-dash].dbo.sysusers
    WHERE (name = N'dbo')
    HAVING (COUNT(*) > 0)

    But is missing the brackets

    Has anyone seen a microsoft patch to fix this?

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I was able to replicate your problem. I haven't seen a patch for this but I am curious why you set the Run As = dbo when the scripts is running under the sa login?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Like Paul, I did the test and came down with the same error. However as Paul stated, if the job is owned by sa why set RunAs to dbo. When you remove that option you can save the job.
    MCDBA

  4. #4
    Join Date
    Jun 2002
    Location
    Hoffman Estates, IL
    Posts
    14
    Thank you for the replies.

    The directions were a "simplified" example to try to find the problem.

    I was writing a script to automate the install of the maintenance jobs on 500+ databases.

    I am trying to move away from "Mixed Mode" authentication, so actually I was planning to have the jobs owned by the same user that SQL Server is running as which may not have the needed privileges. Can a job be owned by and run as SA when you are using NT Authentication?

    The user that a certain application is running as owns many of the databases. I was hoping that running as dbo would simplify the install.

    I know I can look at the owner of each database and set that as the owner of the job, but the job might break when the owner of a database is changed.

    The script does currently work if I have the jobs all owned and run by my personal account, but I did not think that was a good solution. Occasionally servers get set to deny access to all domain administrators, so I was hoping the scripts would still continue to run when that happens.

    Bob S.

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    You are headed in the right direction, and yes a job can be owned by and run as SA.

    If the NT account used by your SQL Server Agent is alias to SA your life will be much easier. I personnaly have worked for only one company that had a problem with this and we resolved it by switching to a server side authenticated id.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Jul 2002
    Posts
    229
    I got the error on line no 248 (rather than 148) when doing the same in T-SQL, see below.
    WHy does it say that "dbo" doesn't exist, when I know it does?


    use msdb

    EXEC sp_add_job @job_name = 'testingtsql',
    @owner_login_name = 'sa'

    EXEC sp_add_jobstep @job_name = 'testingtsql',
    @step_name = 'selecthello',
    @subsystem = 'TSQL',
    @command = 'select hello-world',
    @database_name = 'test-dash',
    @database_user_name = 'dbo'

    Server: Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 67
    The specified @name ('testingtsql') already exists.
    Server: Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near '-'.
    Server: Msg 14262, Level 16, State 1, Procedure sp_verify_jobstep, Line 248
    The specified @database_user_name ('dbo') does not exist.

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Okay, I bet you USD100.00 that you DON'T have a user named 'dbo', but do have a role called db_owner commenly refered to as dbo.

    In your job deffinition you do not need to specify @database_user_name when the job owner is sa, by default the job will run as dbo.
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Jul 2002
    Posts
    229
    You're probably right.

    Anyway, this bug about the dash-name occurs regardless of user, I guess?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •