creating a db user role

Question

creating a db user role

bj007

SSCommitted

Points: 1928
More actions
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

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

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

bj007 SSCommitted Points: 1928 More actions · Answer 3

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.

mccork SSC-Insane Points: 22288 More actions · Answer 4

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

mccork SSC-Insane Points: 22288 More actions · Answer 5

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

bj007 SSCommitted Points: 1928 More actions · Answer 6

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?

mccork SSC-Insane Points: 22288 More actions · Answer 7

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

bj007 SSCommitted Points: 1928 More actions · Answer 8

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

mccork SSC-Insane Points: 22288 More actions · Answer 9

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