How to add Sysadmin login to SQL Server?

sql-server-2008 user-accounts
aspiring picture aspiring · Feb 11, 2013 · Viewed 96.9k times · Source

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

Ian Preston picture Ian Preston · Feb 11, 2013

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

enter image description here

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:

enter image description here

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More