there is no sysadmin

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.

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.

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 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.

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...

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

Did you try logging in with the local admin account? Or with a domain admin account?

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

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

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.

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.

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

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