Post reply

Cannot alter database on Azure

  • January 4, 2023 at 2:58 pm

    #4133549

    Hi there

    We have created a database (empty) on Azure called 'Logger_Groups_CI'

    We then created a user called test2 as follows:

    -- add login

    CREATE LOGIN [test2]

    WITH PASSWORD='XXXX';

    -- add user

    CREATE USER [test2]

    FROM LOGIN [test2]

    WITH DEFAULT_SCHEMA=dbo;

    then i added the user to the roles [dbmanager] and [loginmanager] as follows:

    -- add user to role(s) in db

    ALTER ROLE dbmanager ADD MEMBER [test2];

    ALTER ROLE loginmanager ADD MEMBER [test2];

    GRANT VIEW DEFINITION TO [test2]

    However when i run the following deployment script, i get an error

    SqlPackage.exe /SourceFile:"C:\Users\CAsaipillai\source\repos\metrology\TinytagExplorerMetrology\Logger_Groups.Build\bin\Debug\netstandard2.0\Logger_Groups.Build.dacpac" /Action:Publish /TargetServerName:"xxxx.database.windows.net,1433" /TargetDatabaseName:"Logger_Groups_CI" /TargetUser:"test2" /TargetPassword:"XXXX" /p:AllowIncompatiblePlatform=true

    I get the following error:

    Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 5011, Level 14, State 9, Line 5 User does not have permission to alter database 'Logger_Groups_CI', the database does not exist, or the database is not in a state that allo

    ws access checks.

    Error SQL72045: Script execution error. The executed script:

    IF EXISTS (SELECT 1

    FROM [sys].[databases]

    WHERE [name] = N'$(DatabaseName)')

    BEGIN

    ALTER DATABASE [$(DatabaseName)]

    SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL)

    WITH ROLLBACK IMMEDIATE;

    END

    what am I doing wrong here?

    What should I do to alter user test2 , in order to run this script?

     

     

     

  • January 4, 2023 at 3:09 pm

    #4133565

    Quick question, is this a managed DB instance or a VM instance?

    😎

     

  • January 4, 2023 at 3:13 pm

    #4133566

    Hi

    Its a managed DB instance

     

  • January 4, 2023 at 4:03 pm

    #4133586

    Who created the database "Logger_Groups_CI"? Who is the owner

    From https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16

    dbmanager

    Can create and delete databases. A member of the dbmanager role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the dbmanager role don't necessarily have permission to access databases that they don't own.

  • January 4, 2023 at 4:53 pm

    #4133604

    OK so the owner of the database is a user called 'Ci'

    So does that mean if i wanted to refresh/update the database , then i should be using the user 'CI'?

  • January 5, 2023 at 10:25 am

    #4133941

    If you want to alter the database to enable query store you need ALTER permission on the database.

    Can be through the dbowner or grant a specific user alter database rights

    https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql?view=sql-server-ver16&tabs=sqlpool

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply