How can I add myself to my local sql server's windows authentication?
Solution 1
Unfortunately I think you'll either have to:
- Get your local and beloved IT friend to log in to the database and set up your account correctly.
- Reinstall SQL Server under your account so that you are automatically granted log in permissions.
If you don't have access to the database, then you can't update the security to give yourself permissions.
In order to log in to your SQL Server (individual database permissions may still be missing) the security login needs to be created with Windows authentication
selected and then the domain account name is the Login name
.
Solution 2
And also, since you are a local administrator.
follow microsoft step by step instructions: http://msdn.microsoft.com/en-us/library/dd207004.aspx or as below:
Stop the SQL Server Service.
Open a command prompt as administrator and browse to the SQL Server binn folder (C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn by default)
start the SQL Server in single user mode using the console output
sqlservr.exe -c -m
Then start SSMS or SQLCMD as admin, connect and run the following query:
CREATE LOGIN [DOMAIN\USER] FROM WINDOWS
GO
EXEC master..sp_addsrvrolemember @loginame = N'DOMAIN\USER', @rolename = N'sysadmin'
GO
Related videos on Youtube
KallDrexx
Updated on September 18, 2022Comments
-
KallDrexx almost 2 years
IT recently re-setup my laptop with 64bit windows. He installed Sql Server dev edition for me and set it up with a username and password. I logged in and tried changing Sql Server to windows authentication, restarted sql server, and now it won't let me log in with my domain account.
I have tried adding myself to the SQLServerSQLAgentUser and SQLServerMSSQLUser groups on my local system, but Sql Server still keeps saying my domain account is not authorized. My account is a local system administrator.
How can I get Sql Server to let me log in?
-
KallDrexx over 12 yearsyeah I should have but I thought that all local admins would have access so I didn't think about it.
-
KallDrexx over 12 yearsI figured this was the case. I went to IT and got the local admin user he set sql server up with, reset the sa password. I was thinking that since I was a local admin I could set it up but I guess not. Can't figure out how to get it to recognize my domain account for windows auth though.
-
Bader H. over 12 yearsYou're looking at the server logins correct, and not the database logins? I added a picture to show where you should be configuring the server login.
-
KallDrexx over 12 yearsAh I was looking at the server properties, not the new login screen! Thanks!
-
Antony Koch almost 9 yearsShawn's answer didn't work for me. This one did. +1
-
AnujKu almost 9 yearsCorrect answer !