Post reply

Create new role

  • January 2, 2014 at 11:48 am

    #284088

    I have to create a new role which should have "Select" permission on all tables created by other users and

    2.should have ability to create tables and have access to perform DDL/DML on the tables created by that user.

    This should be on sql 2008

  • January 2, 2014 at 12:08 pm

    #1677029

    balasach82 (1/2/2014)

    I have to create a new role which should have "Select" permission on all tables created by other users and

    2.should have ability to create tables and have access to perform DDL/DML on the tables created by that user.

    This should be on sql 2008

    are you using multiple schemas, or just a single default schema(dbo)?

    DDL? so they can drop tables and columns?

    DML, so they can insert/update/delete?

    sounds like you really want something that's almost a database owner, but not quite?

    USE [YourDatabaseName]

    CREATE ROLE [AlmostOwners]

    EXEC sp_addrolemember N'db_ddladmin', N'AlmostOwners'

    EXEC sp_addrolemember N'db_datareader', N'AlmostOwners'

    EXEC sp_addrolemember N'db_datawriter', N'AlmostOwners'

    --can the users EXECUTE procedures? uncomment if true

    GRANT EXECUTE TO [AlmostOwners]

    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!

  • January 2, 2014 at 1:07 pm

    #1677039

    Thanks Lowell. I followed your steps. Will have to wait and see if there is an issue.

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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