Share via

DBCreator role still available in Managed Instance?

Adrien Uwindekwe 1 Reputation point
2020-06-08T09:50:10.023+00:00

Hi all,

I have an SQL managed instance deployed in Azure.

I am using a longin with dbcreator server role. With this login I would like to create a database but it is giving me the following message. Do you know if I need to have other type of rights to perform to this creation of a database.

For info, I am able to create a database with sysadmin account.

I am using SSMS 18 up to date.

Thanks in advance for your help.

Error that I am getting:

ALTER DATABASE failed because a lock could not be placed on database 'test1'. Try again later.
ALTER DATABASE statement failed. (.Net SqlClient Data Provider)

Complete error message

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

Échec de Créer pour Base de données « test1 ». (Microsoft.SqlServer.Smo)

Pour obtenir de l'aide, cliquez sur : https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.37971.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Créer+Database&LinkId=20476

Emplacement du programme :

à Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
à Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.ApplyChanges(Control marshallingControl)
à Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabase.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
à Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
à Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
à Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
à Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

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

Une exception s'est produite lors de l'exécution d'un lot ou d'une instruction Transact-SQL. (Microsoft.SqlServer.ConnectionInfo)

Emplacement du programme :

à Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
à Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
à Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)
à Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext, Boolean executeForAlter)
à Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingPreferences sp)
à Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

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

ALTER DATABASE failed because a lock could not be placed on database 'test1'. Try again later.
ALTER DATABASE statement failed. (.Net SqlClient Data Provider)

Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=5061&LinkId=20476

Nom du serveur : z-apd-cirru-buenv-uat-ew1-sqlmi.0d4f6cb32b6b.database.windows.net
Numéro de l'erreur : 5061
Gravité : 16
État : 1
Numéro de la ligne : 1

Emplacement du programme :

à Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
à Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

Azure SQL Database

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Atul Kumar 91 Reputation points
    2020-06-08T12:33:17.13+00:00

    Please, remember that the creation of the database is an asynchronous process and even when SQL SERVER Management Studio returned the message that the database has been created, other process may take place in the background for example, enabling TDE, running a backup, etc.. for this new database. If any of this parameter needs to have an exclusive lock during this period of time you could see this error message.

    You may be able to change database settings after sometime when all background processes are finished depending on your environment configurations.

    https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-118-having-alter-database-failed-because-a-lock/ba-p/1105276