• Ian Godfrey (7/9/2010)


    If the AD account is deleted later the login is 'orphaned'. Then the SQL agent jobs stop working, and there are problems creating new tables etc in the database.

    A successful workaround is to make 'sa' owner. This works even if the 'sa' login is disabled, which is default if 'Windows Authentication only' is enabled.

    I have heard that some DBAs rename the ‘sa’ login.

    If the domain account that ownes the database is deleted and the database is orphaned a number of things break. Normally none of them critical and none of them obvious untill you try look at the database properties from Managment Studio.

    If you set SQL Server to run in Windows Only Mode, you don't "disable" the sa account, you only stop people from login on with the sa account and password. The account is "internal" to SQL and can't be deleted or disabled and as far as I know can't be renamed. Something I don't recommend you do.

    The safest and recommended best practice is to have all dbs and jobs owned by sa. There are rare applications that require the database be owned by a spesific account, but you should know about those and I would normally ask the vendor to justify this or fix it, because there is no real reason for it. Normally just bad application implementation.

    Stick to using sa and you'll save yourself some headaches later.

    sp_changedbowner 'sa' Will do the trick.

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.