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 (https://blog.sqlauthority.com)
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??