locked
Granting Permissions RRS feed

  • Question

  • I would like to set up an Administrative Role on my production database that will allow support people to DROP, ALTER and CREATE database objects such as tables, stored procedures, views and user defined functions.  I want to assign this Administrator role to only a few qualified support people.  Does anyone know how I can do this?

    I already have a role that allows View Definition, Select, Insert, Update, Delete and Execute access to objects, I need to give them permissions to Drop, Alter and Create also.  I don't want to grant the db_owner role, I want to restrict their access to user defined objects.

    I am using SQL Server 2005 Enterprise Edition.

    CREATE ROLE [SupportAdmin] AUTHORIZATION [dbo]
    GO
    GRANT VIEW DEFINITION TO [SupportAdmin]
    GRANT SELECT TO [SupportAdmin]
    GRANT INSERT TO [SupportAdmin]
    GRANT UPDATE TO [SupportAdmin]
    GRANT DELETE TO [SupportAdmin]
    GRANT EXECUTE TO [SupportAdmin]
    GO
    EXEC sp_addolemember N'SupportUser_A', N'SupportAdmin'
    EXEC sp_addolemember N'SupportUser_B', N'SupportAdmin'
    GO

    Thanks.

    Thursday, April 1, 2010 3:29 PM

Answers

  • Some combination of the pre-existing database security roles may get you most of the way there...

    For example, grant access to db_datareader for select rights, db_datawriter for insert/update/delete.  You might also check out db_ddladmin and see if it grants too many rights for what you want to do.

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Fred_1000 Thursday, April 1, 2010 7:27 PM
    Thursday, April 1, 2010 4:19 PM