none
User does not have permission to alter database via Job but not Interactive

    Question

  • Enviroment: Windows 2008 SP2, SQL 2008 Standard SP1
    Error:
    Executed as user: SERVER\sqladmin. User does not have permission to alter database 'Census_WebApp', the database does not exist, or the database is not in a state that allows access checks. [SQLSTATE 42000] (Error 5011)  ALTER DATABASE statement failed. [SQLSTATE 42000] (Error 5069).  The step failed.

    Command: Alter Database [Census_WebApp] Set Single_User With Rollback Immediate

    Here is the issue, I am logged onto the server as SQLADMIN (local account) which is in the local server Administrator group and sysadmin on the sql 2008 server.
    sqladmin is also the account that all of the services run under.
    When i run the following command interactively it works fine when i try to schedule the command it fails with the above error.
    I have turned off UAC and just lost at this point.

    Anyone have an idea?

    Thursday, October 22, 2009 2:40 PM

All replies

  •     From your description, most likely the root cause of the problem is that SERVER\sqladmin has administrative privileges in SQL Server via builtin\Administrator membership (i.e. you are relying in the fact that the user full admin privileges in the machine).

      I would strongly recommend against turning off UAC, instead, you can explicitly add this account to the sysadmin role:

    CREATE LOGIN [SERVER\sqladmin] FROM WINDOWS;

    EXEC sp_addsrvrolemember 'SERVER\sqladmin', 'sysadmin';

     

      Let us know if this information helps,
     -Raul garcia
       SDE/T
       SQL Server Engine

    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by JoukoK Saturday, October 24, 2009 6:22 PM
    Friday, October 23, 2009 9:15 PM
    Moderator
  • The first thing that I turn off is UAC.  I'd vote to get Clippy and Microsoft Bob back before UAC.  I put it right at the top of the list of the most annoying and badly implemented pieces of software I've ever seen.  I've yet to see it actually accomplish anything, but it has wasted thousands of hours of people's time clicking OK in that stupid box to launch the application that you wanted to access to begin with.  The single, driving reason for me dumping Vista and vowing that no one in my company would ever run Vista was UAC.  The only reason that I'm even deploying Windows Server 2008 is because I can turn that hunk of junk off.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Monday, October 26, 2009 4:48 AM
    Moderator
  • Raul, No I did not rely on the Administrators group. SQLADMIN was added into SQL Security. I have also tried this as the Domain and Local Administrator with no luck.

    Michael, Uh ya, i completly agree with you. That is the first thing i turn off on any server right after I turn off the Firewall, which I cannot understand for the life of me why microsoft would turn on a firewall on a server by default, let the server administrator turn it on if he needs it.

    Monday, October 26, 2009 12:28 PM
  • Change the job owner to sa and give that a try.  If SERVER\SQLAdmin has been added to the sysadmin role, which would be the case if it is also the service account, you shouldn't be getting that error message.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Monday, October 26, 2009 12:39 PM
    Moderator
  • Well I could not select sa for running the TSQL step for some reason.
    But I did get it working finally.
    Originally the job was created by one of our developers, so I logged on to the machine as SQLADMIN and created the job. It worked.
    I went to the original job and matched all of the security settings with the new one i created and the original job would not run.
    So apparently there is some "hidden" security going on in the background when you create a job.
    Why does security have to be so complicated.....
    Monday, October 26, 2009 1:23 PM
  • There isn't anything hidden.  The job was created by someone who didn't have the authority to perform the action specified in the job.  When the job ran, it executed under the security context of the job owner.  That is what prevented it from executing.  When you created a new job while logged in using an account with sysadmin authority, your account was the owner of the job you created, which when executed would have run under your sysadmin account authority.  You would have had to log in with admin authority in order to change the ownership of a job.
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Tuesday, October 27, 2009 2:40 AM
    Moderator
  • The user who created the job had sysadmin authority though.
    Can't you change the owner of the job after the creation?

    Tuesday, October 27, 2009 12:27 PM
  • Hi

    Tibor has written a good blog post on job ownership and effective permissions

    http://sqlblog.com/blogs/tibor_karaszi/archive/2009/09/19/sql-server-agent-jobs-and-user-contexts.aspx

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Tuesday, October 27, 2009 12:43 PM