Cannot create new user in SQL Server

Question

Everythiing was working fine

My laptop has SQL Server installed

I formatted my laptop

Installed SQL Server (latest version)

then Changed my computer name

Old name was : DESKTOP-EP40R17

New Name is : DELLG3

Now trying to add new user to access databases as i used to do before formatting

I added new user called demo

And setup the user as I did before

but when I try to add new computer name to Securables

I get this error

How to fix that and get a new user working?

I tried deleting the user and create a new user still getting no luck accessing the server with new user id

Edit

I removed the old server name from SQL Server and added new server name using this script

SELECT @@SERVERNAME

sp_dropserver 'DESKTOP-EP40R17'

sp_addserver 'DELLG3', 'local'

Problem still occurs

I cannot login using the new user ID

Here how it looks like now

Here is the error text

===================================

Cannot connect to DELLG3.

===================================

Login failed for user 'demo'. (.Net SqlClient Data Provider)

------------------------------
For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

------------------------------
Server Name: DELLG3
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536


------------------------------
Program Location:

   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

So you've created the SQL Login `demo` and assigned `DeepMed` to be its default database... but have you [created a corresponding User](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql) in the `DeepMed` database and granted permissions to it? — AlwaysLearning, Apr 29 '21 at 09:26
@AlwaysLearning yes, once you create an SQL login a user gets created for the related DBs automatically — asmgx, Apr 29 '21 at 22:51

score 1 · Accepted Answer · edited Feb 09 '23 at 05:14

You will need to restart MSSQL(SQL Server) with SQL Server Configuration Manager(SSCM) to log in with SQL Server Authentication as shown below. This is How to find SQL Server Configuration Manager in your Windows Machine:

Because, you would change "Server authentication" from "Windows Authentication mode" to "SQL Server and Windows Authentication mode", then click on "OK":

Then, you would get this message below:

Some of your configuration changes will not take effect until SQL Server is restarted.

So, as the message says, "Server authentication" is still "Windows Authentication mode" until restarting MSSQL with SSCM. That's why you couldn't log in with SQL Server Authentication, then got the error.

Actually, I got the same error below when I changed "Server authentication" from "Windows Authentication mode" to "SQL Server and Windows Authentication mode", then tried to log in with SQL Server Authentication without restarting MSSQL with SSCM. So, I restarted MSSQL with SSCM, then I could log in with SQL Server Authentication without getting the error:

Cannot create new user in SQL Server

1 Answers1