Database Roles

Question

Database Roles

jarretg

SSCommitted

Points: 1706
More actions
May 19, 2004 at 10:34 am

#161579

Hello everyone,
Say I have a user JohnD in my database. I want JohnD to be able to execute all of the stored procedures in the db, but I don't want to make him a member of the db_owner group.
Is there any way to allow JohnD to execute the procs without running a "grant execute on sproc to JohnD" statement for every stored procedure in the database?
It would be nice if you could create a new fixed database role and specify which actions that role could perform like those listed when you run sp_dbfixedrolepermission. Pretty much what I need is a new fixed database role that is a copy of db_datareader but that also has that extra "EXECUTE any procedure" permission.
Anyone have any ideas? Or am I going to have to explicitly grant execute permissions on each of the stored procedures?
Thanks,
Jarret

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

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

You could create a role....SPUser for example, grant it execute privilege for every stored procedure and then assign users to that role. It would be a lot of work to create the role, but once it was created you would only have to add users to it.

-SQLBill

jarretg SSCommitted Points: 1706 More actions · Answer 2

Thanks for the quick response.

I was looking for an alternate way of handling this (if possible). I didn't want to have to worry about granting permissions every time I rollout new stored procedures for an application. With the fixed database roles, the permissions are automatically assigned to any new objects. That's what I was going for.

That's really not a huge problem to do it the way you suggested, I just know that at some point, I would miss an SP and a user would get an error.

Jarret

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 3

There isn't a way. however you can generate a script to do this automatically.

declare mycurs cursor for

select name from sysobjects

where type = 'p'

and name not like 'dt%'

declare @nm varchar(50)

, @cmd varchar(200)

open mycurs

fetch next from mycurs into @nm

while @@fetch_status = 0

begin

select @cmd = 'grant execute on ' + @nm + ' to me'

print @cmd

fetch next from mycurs into @nm

end

deallocate mycurs

jarretg SSCommitted Points: 1706 More actions · Answer 4

That's one way I was thinking about doing it. Setting up a job that checks for procs without the permissions on it and assigning them appropriately, but the time between job runs could cause a problem.

Oh well, I guess I will have to be careful when moving procs and other objects to other environments. I thought it was worth a post to see if anyone else had come across this before.

Thanks for your help Steve and Bill.

Jarret

neder SSC Eights! Points: 888 More actions · Answer 5

Always include in the code the grant statement after the procedure has been created. This should not only be for procedures but for all the objects as well. This has been the standard practice before (at least in my previous company with Sybase as the backend),

create procedure test.....

go

grant execute...

go