SQL Server Performance

there is no sysadmin

Discussion in 'SQL Server 2005 General DBA Questions' started by hany_buzz, May 13, 2009.

  1. hany_buzz New Member

    Dear all; Currently I’m facing an issue with one of the instances in my company , this instance is running forefront databases, the situation as follow. 1- The server authentication is windows authentication mode,2- There no one in the sysadmin role except the SA account, Now I want to add myself to the sysadmin role and I’m not able any idea please as this is very critical to us.
  2. rohit2900 Member

    Hello Hany,
    Login to the server with a user have administrative rights to the server i.e. may be loca admin or domain admin, this login will automatically have sysadmin rights. not ned to explictly defining it and if you need to add your login with sysadmin permissions create a login using your Domain user id and add it to sysadmin role. Also if you're using windows authentication then disable the sa account.
    Tell me if this helps.
  3. Adriaan New Member

    I wouldn't disable the SA account (not sure that you even can do that) - always a good idea to have a key to the backdoor.
  4. rohit2900 Member

    [quote user="Adriaan"]
    I wouldn't disable the SA account (not sure that you even can do that) - always a good idea to have a key to the backdoor.
    [/quote]
    Just one correction.... If you enables windows autentication (Not the mixed mode). I don't think you can use sa account and it will be disabled.
  5. arunyadav Member

    As 'sa' is SQL Login, if you select Windows Authentication, the 'sa' user will be in disabled status only. Once you change the authentication back to 'Mixed' it will be enabled.
    It depends...how secure is the backdoor entry...:)
  6. hany_buzz New Member

    I didn’t disable the SA but the issue is that to login with sa the authentication must be mixed which isn’t the case
  7. Adriaan New Member

    Did you try logging in with the local admin account? Or with a domain admin account?
  8. hany_buzz New Member

    Adriaan wrote the following post at 05-13-2009 6:11 PM:
    Did you try logging in with the local admin account? Or with a domain admin account?
    yes i'm a local admin but the issue that the BuiltInAdministrators is not in the sysadmin so i don't have any privileges over SQL Server
  9. arunyadav Member

    Hany,

    Your server is running under 'Windows Authentication'. Which means, SQL Server will trust the credentials provided by Windows & validates the account name and password using the Windows principal token in the operating system.
    So, if you login with a local Administrator (System user) or, a domain user with Administrative priveliges on that machine, SQL Server will treat you as an administrator (sysadmin).

    Now, there are two ways to login with your username (with sysadmin rights)

    1) Windows Authentication: Your username should be a member of Administrators of the machine at least if not, domain admin. If yes, then just login to SQL server (select windows authentication in connection popup window.)
    2) Login to the server with a user having administrative rights to the server i.e. may be local admin or domain admin. Change the Authentication to mixed mode and then, create a login for your username.
    How?: You can find it here...http://msdn.microsoft.com/en-us/library/aa337562.aspx
  10. hany_buzz New Member

    you are right the local admins are by default sysadmin but the issue they are revoked as i mentioned nobody is member of sysadmins except the sathat is why even I I’m domain admin i won't be able to grant myself permission on sql or even change the authentication type
    any other ideas please.
  11. Adriaan New Member

    Did I already mention it was nice to have a key to the backdoor?[;)] I think you may have shut yourself out here ...
    Others may chime in with a practical solution.
  12. prabhabk New Member

    look for login mode key in the registry and change the value to 2 that will change the authentication mode to mixed. then restart the sql services and login via SA account
  13. hany_buzz New Member

    Dear ; As I mentioned there is no users in the sysadmin role , so even if I’m a local admin I’ will not be able to change anything

Share This Page