locked
SQL Server 2016: Server role / permissions for "ALTER DATABASE SCOPED CONFIGURATION" RRS feed

  • Question

  • Hi, I'm using sqlpackage.exe with a dacpac file to deploy to a sql server.

    I have a user with just "dbcreator" role. With 2014 there was no issue. When I try to deploy to 2016 it fails, saying that I the user does not have sufficient rights to execute  "ALTER DATABASE SCOPED CONFIGURATION ...".

    1. Is the idea with this new setting that I need a user with sysadmin rights?
    2. If not, what is the minimum that I need?
    3. Is there a way to exclude this setting for SQL 2016 when using sqlpackage?

    Thanks!

    Friday, December 23, 2016 8:54 AM

Answers

  • check user present in master.if is not present create and grant permission as mention by Sunil.

    SELECT *
    FROM sys.sysusers
    WHERE name = 'user'



    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    • Marked as answer by ChristophV Monday, January 2, 2017 11:07 AM
    Friday, December 23, 2016 4:53 PM

All replies

  • Hi Christoph,

    db_creator server role is enough for a login to be bale to create a database even in SQL server 2016. But if this user tries to alter any Database Level Scoped Configuration (New feature in 2016), It require those extra permissions. I believe sqlpackage.exe tries to do that and hence failing. You can grant using below query:

    GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to [UserName]  

    Thanks,


    Kindly mark the reply as answer if they help

    Friday, December 23, 2016 9:56 AM
  • Hi Sunil,

    thanks, this seems to be the correct way. But my server says

    Msg 15151, Level 16, State 1, Line 1
    Suchen des Benutzer-Objekts "..." ist nicht möglich, weil das Objekt nicht vorhand......

    Basically, it can't find the object or I don't have permission.I need to check if I have permission. But, is this command for user or login?

    Thanks!

    Friday, December 23, 2016 1:28 PM
  • check user present in master.if is not present create and grant permission as mention by Sunil.

    SELECT *
    FROM sys.sysusers
    WHERE name = 'user'



    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    • Marked as answer by ChristophV Monday, January 2, 2017 11:07 AM
    Friday, December 23, 2016 4:53 PM
  • ALTER ANY DATABASE SCOPE CONFIGURATION is a database-level permission, and if you are member of db_owner, you have this permission. I don't know what your sqlpackage does, but I would expect that the creator of the database to be db_owner and have this permission.

    I get a little nervous and wonders if the command is issued at the wrong points, so that it tries to change the master database? (In which case, you should thank your lucky star that you don't have permission.)

    Friday, December 23, 2016 10:17 PM
  • Hi Sunil,

    thanks, this seems to be the correct way. But my server says

    Msg 15151, Level 16, State 1, Line 1
    Suchen des Benutzer-Objekts "..." ist nicht möglich, weil das Objekt nicht vorhand......

    Basically, it can't find the object or I don't have permission.I need to check if I have permission. But, is this command for user or login?

    Thanks!

    Hi Chris,

    This get bit tricky now. To be able to grant [DATABASE SCOPED CONFIGURATION] to Login (say ChrisLogin) This Login must be a User into database. Since this login is itself the creator and hence owner of this database and user for this login is not added. You need to executed below query to have this be able to execute [DATABASE SCOPED CONFIGURATION] command.

    use Test
    go
    sp_changedbowner 'sa'
    GO
    CREATE USER [TestUser] FOR LOGIN [TestUser]
    GO
    GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to [testuser] 

    But as you can see, this above query can only be executed when database has been created which sqlpackage.exe does it in one go. You have following option in this case:

    a. Use above method of granting permission if feasible

    b. Grant some higher level of permission to Login who is going to create database so that is also able to SCOPE CONFIGURATION command

    c. Figure out why sqlpackage.exe executed this command and at point it does that. For this use a high privilege account while running sqlpackage.exe and run profiler trace to capture command it executes during whole process.

    d. Figure out a way in sqlpackage.exe so that is does not execute this command at all. This command is not really require and it is used only in case of administrative work or tuning.

    Can you post the command you using with sqlpackage.exe?

    Thanks,

     


    Kindly mark the reply as answer if they help

    Saturday, December 24, 2016 6:30 AM
  • Thanks Vishe, adding the user to master and granting the rights solved it. Also makes sense looking at it now.


    • Edited by ChristophV Monday, January 2, 2017 5:12 PM more details
    Monday, January 2, 2017 11:07 AM
  • Thanks Sunil. Lots of good information here. But the easy fix was to add my user to master and change permissions there. See accepted answer.
    Monday, January 2, 2017 11:08 AM
  • Hi Chris,

    I am glad that it worked for you but I think granting permissions on master was not really required. 

    Cheers!!


    Kindly mark the reply as answer if they help

    Tuesday, January 3, 2017 4:59 AM