Error trying to add a login to Azure SQL Special Role 'dbmanager'



  • Upon running these commands on an Azure SQL instance:

    CREATE LOGIN test WITH PASSWORD=N'Passw0rd'
    GO
    

    ALTER ROLE dbmanager ADD MEMBER test
    GO

    ... I'm getting an error:

    Cannot add the server principal 'test', because it does not exist or you do not have permission.
    

    I've also tried this:

    ALTER SERVER ROLE dbmanager ADD MEMBER test
    GO
    

    I've verified that the login exists. I'm logged in as the server's sysadmin.

    Apparently I was able to get this to work a year ago: https://dba.stackexchange.com/q/295672 But that was then and this is now. It ain't workin'.

    The base issue, however, remains the same: I need to be able to create a database under the scope of a given login and then have that login be able to manipulate the new database (to include DDL), all without manual intervention via db scripts. (This is for use with Entity Framework Code First Migrations, which can be configured to automatically create the database named in the connection string if it doesn't already exist.)

    This capability exists in SQL Server (by adding a login to the dbcreator role), but Azure SQL is proving a tougher nut to crack.

    Also: I've noticed a conflict in the https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=azuresqldb-current#special-roles-for--and-azure-synapse , and I've started an https://github.com/MicrosoftDocs/sql-docs/issues/7502 on the matter. I don't know if it's related to my error, but I thought it worth mentioning.

    How can I enable a login to create new databases in Azure SQL, in a way that the login is automatically added to the new database as a user with read/write/ddl permissions?



  • The dbmanager role is a database role not a server role. That database role only exists in the master database.

    I.e., you have to create a user in the master database for the login in question and add that user to the dbmanager role. As seen below:

    CREATE LOGIN test WITH PASSWORD=N'Passw0rd'
    GO
    

    --Fails:
    ALTER ROLE dbmanager ADD MEMBER test
    GO

    CREATE USER testUsr FOR LOGIN test
    GO

    --Executes successfully:
    ALTER ROLE dbmanager ADD MEMBER testUsr
    GO




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2