How do I add a dba user

Question

I wish to create a database but I do not want it owned by dbo; I think it is poor form to have all objects owned by dbo, instead they show be owned by a schema which is owned by a database.
In my case I want to create a dba user and create the database under that user.
I am running Microsoft SQL Server Developer (64-bit) version 15.0.2095.3; This is installed locally on my laptop so I am not dependent on any network connections.
I expand Security, then Logins, then right click New Login and create my dba user.
I then log out of SSMS, then login with Authentication of SQL Server Authentication, I enter my user (dba) and password.
I get the following error message:

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

Cannot connect to MSI.

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

Login failed for user 'dba'. (Framework Microsoft SqlClient Data Provider)

------------------------------

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

------------------------------

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

------------------------------

Program Location:

at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry, SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

I do not understand the error and have no idea what to try next.
Any assistance would be appreciated.

Answer 1

Hi @Murray Sobol ,
You can check this official link : https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-ver16
I tested the way used through T-SQL, and it worked well. I suggest you to try it cuz it's very convenient.

-- Creates the login AbolrousHazem with password '340$Uuxwp7Mcxo7Khy'.    
CREATE LOGIN AbolrousHazem     
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';    
GO    
  
-- Creates a database user for the login created above.    
CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem;    
GO  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 2

Your answer is NOT correct. the same error is produced.

Answer 3

Hi @Murray Sobol ,
I tested it was ok. So I assume it was the connetcion problem, you can check this official link ：https://learn.microsoft.com/en-gb/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver16 ,which shows the solution. I hope this can be helpful for you .