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. The follow up question was how to add a user to the SysAdmin role.
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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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..
Thanks for the quick and easy instructs!!
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???
THANK U SIR
the post helped quite well & saved time……
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 :)
Great, 100% useful! Thx
hi,
I want to know the access a user can have, If I give a user sysadmin role like select,alter,modify
what role required if we want to provide sysadmin role to other users
Hi i want a query like how to remove the SysAdmin permission for a user.Please help me on this any one
I don’t know how to show my gratitude .this was big help for me thanks
That’s great to hear montu
How Can I contact you if I have sql server problem?
My email address is on every single page.
ur the best
how can i add all list as sysadmin??