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)

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

  • installed SQL Server 2005 Express Edion. But in VIEW options, i can not find SOLUTION EXPLORER and TOOLBOX.
    What kind of problem is that ??? Help me to fix it pls, it will be apreciate so much.
    Thanks

    Reply
  • Method 1 is not working in SQL Server 2005
    and getting the below error….

    Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near ‘LOGIN’.
    Msg 15002, Level 16, State 1, Procedure sp_addsrvrolemember, Line 17
    The procedure ‘sp_addsrvrolemember’ cannot be executed within a transaction.

    Reply
  • Hi Pinal,

    I have a situation where someone asked me to check data in a table by name “Material” over phone. But when I checked for the table in the database, it was not there. I called back that guy to confirm the tablename and he said “I am sorry. The tablename was Materials”. He added the letter “s” to the table name.

    The point I am trying to make here is…..at the time of writing a query in particular database, would it be a good idea to have some kind of color coding which tells me if the table exists at all while typing the query itself. Otherwise, I am wasting my time typing the whole query and receiving an error message……..I hope you got my point…….

    Reply
  • Hi Srinivas,

    Please compile the below procedure in master datbase.

    CREATE procedure dbo.sp_f
    @name varchar(40),
    @type varchar(2)
    as
    begin
    if @type in (‘U’,’V’,’S’)
    begin
    select ‘select * from ‘+name from sysobjects
    where type=@type
    and name like ‘%’+@name+’%’
    order by name
    end
    if @type in (‘P’,’TR’)
    begin
    select ‘sp_helptext ‘+name from sysobjects
    where type=@type
    and name like ‘%’+@name+’%’
    order by name
    end

    end

    Then you can call exec sp_f ‘mate’,’u’

    if will list down all tables that contains the key word mate. Hope this solves your problem.

    Reply
  • Hi Pinal,

    If we want to add a Domain User as a Sysadmin on SQL.
    Do we need to create Login in SQL and then add it as a Sysadmin?

    I tried to add domain user directly as a sysadmin and it also created as Login(user) in SQL.

    Command i used:
    sqlcmd -S MachineName\SQLEXPRESS -q “EXEC master..sp_addsrvrolemember @loginame = N’Domain\User’, @rolename = N’sysadmin'”

    Aniket

    Reply
  • Dave
    Let me know if there is a way to do this in sql server 2005

    I have a DB server in which i will be adding/dropping users very frequently as per the requirement. Now i would like to implemet this from a stored proc in which it has to do the follwing.
    1. List users in a DB
    2. Add user to the DB with one of the Db role
    3. Remove any users if required
    4. Run a script to lock few tables in the db for that particular user.

    I am not sure if this is possible from a store proc but would really like to do so, I understand it involves has admin previlages but i thougt of creating a sql user with admin access to run this proc.

    Reply
  • Earl WIlliams
    April 15, 2010 4:40 pm

    Great, I thought I was at a loss and had to re install and reconfigure. I love my tech community!

    Reply
  • vaithianathan
    March 22, 2011 3:20 pm

    hi pinal,
    it’s very useful to me

    Reply
  • vaithianathan
    March 22, 2011 3:24 pm

    hi pinal, now i am learning sql, please give me some tricks to understand and write queries…

    Reply
  • Hi,

    Thank you. It has helped me a lot for granting rights to the user. It is simpler to understand and do it.

    Thanks a lot
    Manaa

    Reply
    • hi, can the database be open for users, means can any user add data. for example, currently running on access 2007, any employee can add there jobcard. If I migrate to sql2008, is this possible. pls reply. thanks. [email removed]

      Reply
  • Hi Dave, I have followed your posts etc and used your wonderful knowledge over the years (won’t say how many). I have been away from DBA for awhile. I’m working in SQL 2008 first time and can not get a sysadmin setup on the sql instance.

    When sql was installed a sysadmin was setup. I can log into the server with this admin account and then connect to SQL. I then added myself (windows acct – domain admin) to sql as sysadmin. Then I tried logging into the server as myself and connecting to sql as myself. No go…. I get the following error:

    A network-related or instance-specifi error occured…. you know it. Since I can connect this way with the Domain account that was setup as sysadmin at install I know this works and remote connections are setup etc.

    There are 2 instances and I added myself to both, what am I missing? Is there an AD component? Do I need to restart sql services after adding users (not in my experience) do I have to log off and on?

    Thanks

    Reply
  • Reply
  • “The role does not exist in the current database” what is this error while adding server rol to database???

    Reply
  • hi every body

    i want create user in sql server 2005 to only access a one table

    how i can do it

    Reply
  • Hello, I have a server on a machine which has had it’s name changed (has moved from being a local only machine to one connected to a domain with a new name. The database is not letting any account access to it. I am trying to create those permissions. The only user with sysadmin rights on the server is sa. The password for this has gone missing.
    I have followed your instructions above for creating a new sysadmin, using sqlcmd run as administrator on the machine. I get the following error at the create login line:

    msg 15247, Level 16, State 1, Server MYSERVERNEWNAME, Line 1
    User does not have permission to perform this action.

    Is there anything i can do to either create a new sysadmin, or sort out the issue with the name change?

    Reply
  • i have installed sql server 2008 but i am not able to create a database in it
    its throwing an error like permission denied to create a database. so can any one help me out in solving this

    Reply
  • Dishant Shah
    June 4, 2012 4:55 pm

    I have to do Same thing from Command Prompt

    Reply
  • can a user be w/ sysadmin role but deny user from truncate or delete?

    Reply
  • can you please help me to create new user with Mixed Authentication?

    Reply
  • Mahendra Prasad
    February 23, 2013 7:58 pm

    can any one tell me what type of sql code used by tester. since i am new in testing to what i need to practice for?????????

    Reply

Leave a Reply