Post reply

creating a db user role

  • June 19, 2003 at 11:08 am

    #81843

    Can I create a db role and assign the existing roles to it. Like, db_datareader,db_datawriter,db_ddladmin needs to be given to my new role ? thanks

  • June 19, 2003 at 12:30 pm

    #462234

    Hi bj007,

    quote:

    Can I create a db role and assign the existing roles to it. Like, db_datareader,db_datawriter,db_ddladmin needs to be given to my new role ?

    Seems like reinventing the wheel? Any reasons why? And what about the other way?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 19, 2003 at 12:46 pm

    #462245

    Yes, you can nest roles. You'll want to make the user-defined role (the one you create) a member of the fixed database roles... not the other way around as pointed out.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    Edited by - bkelley on 06/19/2003 12:47:11 PM

    Edited by - bkelley on 06/19/2003 12:47:39 PM

    K. Brian Kelley
    @kbriankelley

  • June 19, 2003 at 5:02 pm

    #462302

    All I want to do is to give users read,write and execute permission on the database. So I thought of creating a new db role and assign db_reader,db_writer and ddl_admin roles.What should I do then?

    By the way, I restored a production dump to test environment. Stored procedures which has execute permission to public in production, no longer has that permission in dev. What should I do to restore all the permissions? thanks.

  • June 19, 2003 at 5:24 pm

    #462305

    Be careful of assigning db_ddladmin for the purposes you've described. It will give the users permission to manipulate object definitions (tables/views/SPs), but not the execute permissions you require.

    Unfortunately SQL doesn't have a db_ExecuteAnyUserProcedure role. You need to grant execute rights on each stored proc.

    The public execute permission problem is a weird one. Often seen it for other users because SID mismatches after restoring on another server, but it (theoretically) shouldn't happen for PUBLIC.


    Cheers,
    - Mark

  • June 19, 2003 at 5:25 pm

    #462307

    Be careful of assigning db_ddladmin for the purposes you've described. It will give the users permission to manipulate object definitions (tables/views/SPs), but not the execute permissions you require.

    Unfortunately SQL doesn't have a db_ExecuteAnyUserProcedure role. You need to grant execute rights on each stored proc.

    The public execute permission problem is a weird one. Often seen it for other users because SID mismatches after restoring on another server, but it (theoretically) shouldn't happen for PUBLIC.


    Cheers,
    - Mark

  • June 19, 2003 at 5:29 pm

    #462309

    If a user is executing a stored procedure which internally creates a temporary table, then i need to give ddl_admin privilege in the current db and tempdb. Is itn't it?

  • June 19, 2003 at 5:38 pm

    #462312

    You only need to allow access the the user db, and optionally place them in a user-defined role. For the SP in that db that creates a temp table, just grant execute permission to the user, or to public (the lazy way ), or to the user-defined role. No tempdb permissions are required.


    Cheers,
    - Mark

  • June 19, 2003 at 5:50 pm

    #462314

    Thanks for your info.

    By the way, to fix the public execute permission, I copied an existing script in this site, and modified it:

    declare @procname varchar(100), @command varchar(100)

    declare Crs insensitive cursor for

    select name as ProcName from sysobjects where type = 'P' order by name for read only

    open Crs

    fetch next from Crs into @procname

    while @@fetch_status=0

    begin

    select @command='grant execute on '+@procname+' to public '

    exec(@command)

    fetch next from Crs into @procname

    end

    close Crs

    deallocate Crs

  • June 19, 2003 at 5:58 pm

    #462315

    Apologies... I could have assisted in the re-creation of the PUBLIC permissions. But I got too tied up in the intrigue as to how they were lost in the first place.


    Cheers,
    - Mark

Viewing 10 posts - 1 through 9 (of 9 total)

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