SQL Server Management Studio Windows Authentication Issue

Question

Hello,

I am running SQL Server Express (2017) with SQL Server Management Studio (V18.8).

As login = User, I used server login and create a sample database "Chinook". It works fine. But I exit and login again using Windows Authentication, which implicitly for User, I cannot access that database. Can you please tell me how to resolve it?

An issue could be related to the above issue is that I login as desktop administrator login to SQL Server Management Studio, and grant User the permission to create database. But when I use Windows Authentication, I cannot create database. Instead I have to use server login as User to create the database Chinook. When I login as my desktop administrator login, I find the create database permission check box for User is unchecked.

Thank you!

Answer 1

I uninstalled the existing SQL Server Express, and SQL Server Management Studio, and installed SQL Server Express 2019 and SQL Server Management Studio 18.8. Then I found the correct login full name for User is [BUILTIN\Users].

With this [BUILTIN\Users] and using Windows Authentication I can create and access databases without issue after grant proper permissions.

Thank you ErlandSommarskog for your time to help me resolve this issue!

Answer 2

I may not following exactly what you are doing, but it seems that you have an SQL login and a Windows login with the same name. Or well, not exactly the same name. The name of the SQL Login is User, and the name of a Windows login is MACHINE\User or DOMAIN\User. The name of a Windows login in SQL Server always start the name of the machine or the domain followed by a backslash.

Thus, these are two separate entities.

Just because you are administrator in Windows does not mean that you are sysadmin in SQL Server - and for very good reasons, as being Windows and SQL Server administrator are two separate duties.

If you want your Windows login to have sysadmin permissions on the instance, run this command with your SQL login:

ALTER SERVER ROLE sysadmin ADD MEMBER [Domain\User]

If you are not in a domain, use the machine name.

Answer 3

Thank you so much for the quick reply!

My computer login Name is User. In SSMS I also have a login name User. In SSMS, my instance name is DESKTOP-SV0JU22\SQLEXPRESS. When I use Windows Authentication to login SSMS, the connection above database shows:

DESKTOP-SV0JU22\SQLEXPRESS (SQL Server 14.0.203 - DESKTOP-SV0JU22\User

Then I follow your instruction:

ALTER SERVER ROLE sysadmin ADD MEMBER [DESKTOP-SV0JU22\User]

It gives an error:

Msg 15151, Level 16, State 1, Line 1
Cannot add the server principal 'DESKTOP-SV0JU22\User', because it does not exist or you do not have permission.

Thanks again!

Answer 4

I am trying to use vb.net to connect to SQL Server. With Server Login, it is successful and do query. But with Windows Authentication, I can connect successfully, but cannot access database table to query.

Using Windows Authentication to query is one of the task that end user asked for.

Answer 5

OK, I'll try it. If it still does not work, I'll uninstall the SQL Server and reinstall it to see whether it resolves the issue.

Thank you very much!