How to add user to sysadmin role or to any other fixed server role

We can add users to sysadmin role or to any fixed server role using T-SQL or using SSMS(SQL Server Management studio) here are the both:

1.       Using T-SQL method

--Create SQL user account Login

USE [master]

GO

CREATE LOGIN [tesuser] WITH PASSWORD=N'test@123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

--Create windows user account login

USE master

GO

CREATE LOGIN [DominName\Username] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

Note: Keep in mind as it is for test purpose I have given simple password, in any live enviorment try and keep complex passwords and if your environment permits use Expiration and policy options with SQL users(Via Active directory we can set password policy for windows users) for better security.

--Add user to sysadmin role

USE master

GO

EXEC master..sp_addsrvrolemember @loginame = N'User Name', @rolename = N'sysadmin'

Note: user name can be SQL user or Window user.

2.       GUI Method

Go to SSMS in the object explorer right click on Security then click on New and then Login you will get following screen

                                              

Key in the login name based on Authentication type (Window or SQL) and password if your are creating SQL Server authenticated user and then click on the Server roles (you can see it on your left hand side) and select sysadmin(shown below) and click OK.

3.       We can test by using following query for accuracy.

SELECT

   spm.name AS 'User Name',

   spr.name AS 'Server Role'

FROM

   sys.server_role_members srm

INNER JOIN

   sys.server_principals spr

   ON spr.principal_id = srm.role_principal_id

INNER JOIN

   sys.server_principals spm

   ON spm.principal_id = srm.member_principal_id

WHERE spm.name = 'testuser'