Post reply

Replacement of role db_owner

  • September 24, 2018 at 5:05 am

    #408543

    Hello
    I am working on a folder removing the db_owner role for some users and replacing it with a specific rolefor that I created this role
    CREATE ROLE [R_SQL_dev]GOgrant select, insert, update, delete, execute to [R_SQL_dev]
    grant create table, create procedure, create view, create function, create type to [R_SQL_dev]
    grant alter any schema to [R_SQL_dev]

    now the dev responsibility to my request to let the devellopeur standalone on the instrunction drop database
    I add this instruction
    grant ALTER ANY DATABASE to [R_SQL_dev]

    and the syntax of the role becomes
    CREATE ROLE [R_SQL_dev]
    GO
    grant select, insert, update, delete, execute to [R_SQL_dev]
    grant create table, create procedure, create view, create function, create type to [R_SQL_dev]
    grant alter any schema to [R_SQL_dev]
    grant ALTER ANY DATABASE to [R_SQL_dev]

    now if i execute the creation script i just got this error

  • September 24, 2018 at 7:53 am

    #2006735

    joujousagem2006 1602 - Monday, September 24, 2018 5:05 AM

    Hello
    I am working on a folder removing the db_owner role for some users and replacing it with a specific rolefor that I created this role
    CREATE ROLE [R_SQL_dev]GOgrant select, insert, update, delete, execute to [R_SQL_dev]
    grant create table, create procedure, create view, create function, create type to [R_SQL_dev]
    grant alter any schema to [R_SQL_dev]

    now the dev responsibility to my request to let the devellopeur standalone on the instrunction drop database
    I add this instruction
    grant ALTER ANY DATABASE to [R_SQL_dev]

    and the syntax of the role becomes
    CREATE ROLE [R_SQL_dev]
    GO
    grant select, insert, update, delete, execute to [R_SQL_dev]
    grant create table, create procedure, create view, create function, create type to [R_SQL_dev]
    grant alter any schema to [R_SQL_dev]
    grant ALTER ANY DATABASE to [R_SQL_dev]

    now if i execute the creation script i just got this error

    You need to be in master when you execute ALTER ANY DATABASE as this is a server scoped permission.

    Sue

  • September 24, 2018 at 7:56 am

    #2006738

    ALTER ANY DATABASE expects a [login] as the target, and not a database(user) role.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 24, 2018 at 1:41 pm

    #2006812

    CREATE ROLE [R_SQL_dev]
    GO
    grant select, insert, update, delete, execute to [R_SQL_dev]
    grant create table, create procedure, create view, create function, create type to [R_SQL_dev]
    grant alter any schema to [R_SQL_dev]
    GRANT CONTROL TO [R_SQL_dev]

  • September 24, 2018 at 2:24 pm

    #2006823

    You could create a server role for alter any database. If this is your development server you may want to use a login created from a domain group and add the developers to it. If you run the database level grants in model all database created will have that role with those permissions.

  • October 1, 2018 at 7:27 am

    #2007495

    I just had this error when I try to create a table in graphical mode

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

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