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

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

28 thoughts on “SQL SERVER – Add Any User to SysAdmin Role – Add Users to System Roles

  1. 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

  2. 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.

  3. 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…….

  4. 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.

  5. 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

  6. 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.

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

  8. 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

  9. 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?

  10. 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

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

  12. 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?????????

  13. 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..

  14. 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???

  15. 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 :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s