SQL SERVER – Add Any User to SysAdmin Role – Add Users to System Roles

The reason I like blogging is follow up questions. I have wrote following two articles earlier this week. I just received question based on both of them. Before I go on questions, I recommend to read both of the article first. Both of them are very small article so they are quick to read.

SQL SERVER – DISABLE and ENABLE user SA

SQL SERVER – Fix : Error : Msg 15151, Level 16, State 1, Line 2 Cannot alter the login ’sa’, because it does not exist or you do not have permission

The question I had received is following: “How to add any user to systemadmin role?

This is very simple process.

Method 1: T-SQL

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'test', @rolename = N'sysadmin'
GO

Method 2:  SQL Server Management Studio

SQL SERVER - Add Any User to SysAdmin Role - Add Users to System Roles sysadmin1

SQL SERVER - Add Any User to SysAdmin Role - Add Users to System Roles sysadmin2

SQL SERVER - Add Any User to SysAdmin Role - Add Users to System Roles sysadmin3

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Error Messages, SQL Scripts, SQL Server, SQL Server Security
Previous Post
SQL SERVER – Fix : Error : Msg 15151, Level 16, State 1, Line 2 Cannot alter the login ‘sa’, because it does not exist or you do not have permission
Next Post
SQL SERVER – Fix : Msg 15151, Level 16, State 1, Line 3 Cannot drop the login ‘test’, because it does not exist or you do not have permission

Related Posts

37 Comments. Leave new

  • i have one doubt in providing user permissions, we have a silcan/DBadmin sa group, this group have a 11 members i need to provide the READER permission for one of the member on this group “silcan/DBadmin”, how can i provide the permission ,could you please help me..

    Reply
  • Omar Vasquez
    June 25, 2013 6:20 pm

    Thanks for the quick and easy instructs!!

    Reply
  • I am trying to write a connection string to access data in a SQL Server from an ASP file.

    Following your article, I went to the Security/Logins folder, right-clicked and chose New Login. I created a user (Rtd_User1) with a password. I set the default database to the database to which rights should be limitied (Trip_Denver-OD)

    Here is the current connection string:

    Connect.Open “DRIVER={SQL Server};SERVER=SQL-SERVER82011;DATABASE=Trip_Denver-OD;UID=Rtd_User1;PWD=Rtd01”

    And the resulting error:

    Error: -2147467259 – [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database “Trip_Denver-OD” requested by the login. The login failed.

    (I have tried the database name with and without brackets.)

    ******************

    I have also tried to create a DSN on the web server. If I use the “With Windows NT authentication using the network login ID” the connection passess the test. But if I use the “With SQL Server authentification …”, check the “Connect to SQL Server to obtain …” and put in those credentials, the test fails.

    When I use this connection string:

    Connect.Open “DSN=Trip_Denver-OD_SQL;Database=[Trip_Denver-OD];UID=Rtd_User1;PWD=Rtd01;”

    I get this error:

    Error: -2147217843 – [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.

    I suspect that’s because I’m using the “Windows NT authentication” instead of the “Connect to SQL Server” but the latter doesn’t work.

    There MUST be a simple way to do this! What am I missing???

    Reply
  • THANK U SIR
    the post helped quite well & saved time……

    Reply
  • Hello Folks,

    Can anyone please help me as how to grant/remove sysadmin role for Administrator in SQL SERVER 2008 R2?
    It would be of great help if anyone responds at the earliest..please do :)

    Reply
  • Great, 100% useful! Thx

    Reply
  • hi,

    I want to know the access a user can have, If I give a user sysadmin role like select,alter,modify

    Reply
  • what role required if we want to provide sysadmin role to other users

    Reply
  • satish chadalavada
    April 20, 2016 12:03 pm

    Hi i want a query like how to remove the SysAdmin permission for a user.Please help me on this any one

    Reply
  • I don’t know how to show my gratitude .this was big help for me thanks

    Reply
  • How Can I contact you if I have sql server problem?

    Reply

Leave a Reply