Post reply

generic db role (probably the oldest question in the book)

  • July 24, 2006 at 9:13 am

    #103207

    Is there any db role that can be assigned to a db user which gives them everyright in a database except the ability to alter, drop, create objects in the db?? Or do I just need to give them datareader and explicitly grant persmissions on every table, view, sproc, etc??

    much obliged for any info,

    al

     

  • July 24, 2006 at 9:19 am

    #651097

    Hi Nick,

    If you mean that the user needs to select / modify data then you can go ahead with "db_datareader" and "db_datawriter".

    Thanks


    Lucky

  • July 24, 2006 at 10:22 am

    #651135

    Hey there. Thanks for the advice. I tried datawriter but that still doesn't give me execute permissions on the sprocs.

  • July 24, 2006 at 11:23 am

    #651151

    You have to explicit set GRANT on every SP you want the user to have acccess to.

    N 56°04'39.16"
    E 12°55'05.25"

  • July 25, 2006 at 3:31 am

    #651278

    If you are using SQL 2005 you can very easily create a db_executor role that gives SP execute permission to all SPs in a database (including ones created after you create the role); you don't have to explicit GRANT on every SP:

    USE database

    GO

    CREATE ROLE db_executor

    GRANT EXECUTE TO db_executor

    GO

    EXEC sp_grantdbaccess 'SQLUser'

    GO

    EXEC sp_addrolemember 'db_executor', 'SQLUser'

    EXEC sp_addrolemember 'db_datareader', 'SQLUser'

    EXEC sp_addrolemember 'db_datawriter', 'SQLUser'

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

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