Azure SQL data pool access for user

Question

Hello

I have a recent issue. Recently I was creating new sql databases from elastic pool and we have 2 users in database master -like slqadmin* and sauser* ( example) 1st is given by default by Azure and second is application user. Before recently all was fine and when I created new databases - all rights were ok. But now I see that new databases from same elastic pool and region and even same endpoint - lack that access . I thought that is my mistake - and gave to recent DBs rights manualy, but now I face problem that that user cannot operate master db. I never touched before master db . and I assume that It can be related to creation of some DBs .. here is an error - could someone bright , please bring some light on this? =)))

Error Number:916,State:2,Class:14
2023-06-02 12:20:01 +02:00 [Error] :System.Data.SqlClient.SqlException (0x80131904): The server principal "sasqlintegration" is not able to access the database "master" under the current security context.
Cannot open database "master" requested by the login. The login failed.
Login failed for user 'sasqlintegration'.
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   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.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext)
   at System.Data.Entity.SqlServer.SqlProviderServices.<>c__DisplayClass60_0.

Answer 1

Hi Kostiantyn Bezruchenkov •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you are getting login error: The server principal "sasqlintegration" is not able to access the database "master" under the current security context. Cannot open database "master" requested by the login. The login failed.

Could you please try below:

-- in the master database we need to create LOGIN  
create login [MyLogin] with password = 'DummyPassword'  
  
-- in non-master database including the master database, we need to create a USER, so we will be able to use it by the LOGIN  
-- This mean that in each database we add this the person will be able to connect, but still will have no permissions to do anything 
-- Maybe USER is only created in the master database but a USER is a databases level entity  
create user [MyUser] from login [MyLogin];

Now we are moving to the permissions which you want to give your LOGIN/User. Here are some options for example:

-- if you want the user to be able to create databases, then in the master database  
exec sp_addRoleMember 'dbmanager', 'MyUser';  
  
-- if you want the user to be able to create other LOGIN, then in the master database  
exec sp_addRoleMember 'loginmanager', 'MyUser'  
  
-- in each individual database, that you want to give the USER full control in the database level, grant him db_owner  
exec sp_addRoleMember 'db_owner', 'MyUser';

Note! Give the USER only the permission that he need and nothing more! The above is only a sample of a few permissions. Use GRANT to give permissions on specific entities in the database and don't make each USER a database owner.

For more information you should check the following document:

https://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage

https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles

https://stackoverflow.com/questions/19009488/the-server-principal-is-not-able-to-access-the-database-under-the-current-securi

Hope this helps. Please let us know if you are still facing issue so that we can further look into it.

If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Thank you.