locked
SQL Server 2005/2000 (JOB running through Domain level Id getting fail ) RRS feed

  • Question

  • Dear All,

    In our production enviroment one new audit is implement from company pocily side.
    all SQL Server aunthication account should be disable.

    Now I have created 2 a domain level Id one foe sysadmin role & second for public role at SQL server level.
    sysadmin id added in "Deney Intractive logon right" at windows level & "allow logon locally" aslo as per company policy.

    when I try to connect with SQL Server level with sysadmin Id every time I have remove from "Deney Intractive logon right" ...

    I am using runas command for connecting bec as per company policy I never connect with windows level with this Id.

    please give me solution for checking the job at database level or how to connect from command prompt  without removing from  "Deney Intractive logon right" or how to connect remotely from GUI mode. 

    2. How to disable sa login in sql server 2000.

    Regards
    ravi



     

    india
    • Edited by MSSQL DBA Friday, November 20, 2009 3:45 PM
    Saturday, November 7, 2009 10:56 AM

All replies

  • Hi Ravendra,

    I'm not entirely clear on the exact issue you're running into with the account permissions, but I'll try my best to answer. You should be able to use runas to launch either sqlcmd or osql from a command line (or perhaps even a command window) as the account that doesn't have permissions. It may still need some basic permissions on the machine you're going to use runas from though, but I don't recall ever having to do anything. I know I've done something like this to test authentication for accounts that are deined local login rights (aka service accounts) in the past so it should work. Windows 2003/2008/XP.

    There is no way that I'm aware of to disable the sa login in SQL 2000 wtihout switching the server to Windows Authentication only. It's been a loooooooong time since I've touched SQL 2000 though (hooray!) so I could be forgetting something. I'm pretty sure though that renaming/disabling the sa account was one of the great security improvements that came with SQL 2005. Watch out for issues with service packs and cumulative updates if you go down this route.

    -Andrew
    • Proposed as answer by A.Lockwood Sunday, November 8, 2009 5:03 PM
    Sunday, November 8, 2009 5:02 PM
  • lockwood,

    Is that possible we can input file by using damin level Id.

    exp Domain Id is : Domain\XX
    SQL Instance name:- INSTANCE
    Password:- PPPPPPPPPP
    input file is input.sql located in D:\temp
    output file we need to move in C:\temp

    how to connect with runas command & take a input file then generate the output file.

    Regards
    Ravi

    india
    Monday, November 9, 2009 12:08 PM
  • We have created domail level Id domain\comp & I have mapped in all SQL Server with sysadmin role this is equivalent to SA login..
    we have removed Builtin\Admin accout as per our company policy..

    this domain level Id only for sysadmin work (If there is any change at Db level)

    Now when I trying to running job through domain\comp user (JOB Owner) it's getting fail..

    Message
    The job failed.  Unable to determine if the owner (domain\comp) of job Every1_hours_no_of_users has server access (reason: Could not obtain information about Windows NT group/user 'domain\comp', error code 0x5. [SQLSTATE 42000] (Error 15404)).

    domain\comp for this Id we have not assign any permission for data & log file simple map with sp_grantlogin with sysadmin role..

    please advice..

    Regards
    Ravi
     
    india
    Friday, November 20, 2009 3:50 PM