How to add Sysadmin login to SQL Server?

Question

I have installed SQL Server 2008 using Windows Authentication in my laptop for my own use. I want to add Sysadmin account/role using SQL Server Login type. I checked this post, but it's not showing what I need. How can I add the sysadmin account? By right shouldn't it be the default role/login?

Answer 1

sysadmin is a server role; it can be applied to any login.

When installing SQL Server a login sa is created with this privilege; you can specify the password when you're installing SQL Server.

Or you can create your own login:

CREATE LOGIN adminuser WITH PASSWORD = 'ABCDegf123'; GO  EXEC master..sp_addsrvrolemember @loginame = N'adminuser', @rolename = N'sysadmin' GO 

This all assumes Mixed-Mode authentication is set up to allow SQL logins. Change Server Authentication Mode.

After comment:

So from the link above to change to Mixed-mode in Management Studio you would:

1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

4. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

It should look something like this:

Answer 2

'sysadmin' is a role, 'sa' is the 'system administrator' sql-login.

If you installed just using Windows Authentication (default mode), the 'sa' account is already there, but will be disabled by default. Look under YourServerName -> Security -> Logins in Management Studio, and you should see 'sa' with a down-arrow in the icon (symbolises a disabled user)

You have to log in using an account with sufficient privileges (if your installation account has sysadmin privileges that will work), then enable 'sa' and set a password.