none
MS SQL Server 2016 Roles & rights for executing a procedure in different databases RRS feed

  • Question

  • Hi all,

    my question is:

    i have a procedure in database DA.dbo.udp_1 which is actually doing some inserts in DA2.dbo.log and dropping and recreating objects in DA3.dbo schema.

    My user SCHEDULER has db_datareader and db_datawriter rights on DA, DA2, DA3 and GRANT EXECUTE on schema DA.dbo.

    The procedure is actually failing due to the mentioned DDL operations in DA3. What is necessary for running it? Is a GRANT ALTER on DA3.dbo schema sufficient or do i have to switch to db_ddladmin (or anything else)?

    Currently i have no rights to test it, but with db_owner permissions the procedure executed successfully before.

    Thanks in advance.

    Wednesday, January 8, 2020 1:33 PM

All replies

  • Hello Friend,

    You're right. You will need the 'db_ddladmin' permission on this database as you make changes such as rebuilding objects (ddl comands).

    If this answer has helped you, mark it helpful so that someone else with a question or similar problem can find an answer or help more easily. Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Database Analyst - Sql Server and Oracle]
    • Edited by Jefferson Silva DBA Wednesday, January 8, 2020 3:30 PM translate
    • Proposed as answer by Lily Lii Friday, January 10, 2020 1:30 AM
    Wednesday, January 8, 2020 3:30 PM
  • Hi Friend,

    As Database-Level Roles said, Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. So you may switch to db_ddladmin and try again.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, January 9, 2020 8:03 AM
  • Hi Friend,

    Do the answers above help you? Please feel free to let us know if you have any other question.

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, January 10, 2020 1:29 AM