I need to take about 300 SP's and strip out whatever permissions are
assigned to them - then add one role:
db_procexecutor_write to each one.

For example - at the bottom of this SP is:
.....
go
GRANT EXECUTE ON dbo.p_CnsAct_i TO WINADMIN
go
GRANT EXECUTE ON dbo.p_CnsAct_i TO WINCNS
go
GRANT EXECUTE ON dbo.p_CnsAct_i TO WINCNSSUPV
go
GRANT EXECUTE ON dbo.p_CnsAct_i TO WINCOMPILEALL

When I'm done - it should only have:
GRANT EXECUTE ON db_procexecutor_write

news.microsoft.com wrote:
If you no longer require the roles:
1- Drop each role using sp_droprole
2- Change the GRANT statements in each proc and re-execute, or manually
grant rights to each procedure to the new role

If you need to keep the roles:

1- Insert the names of all the procedures into a temp table
2- Loop through the temp table using a cursor
3- In the loop, revoke the rights from each group and grant the new
rights


--
David Gugick
Imceda Software
www.imceda.com

This approach still requires manually adding my role to each proc...I know
there is a way to programatically do it...

"David Gugick" <davidg-nospam (AT) imceda (DOT) com> wrote

news.microsoft.com wrote:
Here's a little more to get you started:

-- Create a test proc
create proc dbo.sec_test
as
print 'security test'
Go

-- create a new server role
Exec sp_addrole 'TEST_ROLE'
Go

-- grant execute on the proc to the new rolw
grant execute on dbo.sec_test to TEST_ROLE
Go

-- get the role id
Exec sp_helprole 'TEST_ROLE'

RoleName RoleId IsAppRole
---------- ------ -----------
TEST_ROLE 16400 0

-- use the RoleID in the following query in order to return all procs in
the current database
-- that the role has access to. This is the query to get the list of all
procs
select id, object_name(id) as "object_name"
into #procs
from dbo.syspermissions
where grantee = 16400
and ObjectProperty(id, 'IsProcedure') = 1

Select * from #procs

id object_name
----------- -----------
1015062752 sec_test

Using the temp table, run a cursor through all rows and revoke rights on
each procedure using dyamic SQL. Grant execute rights to the new role.


--
David G.

This was my solution after first using EM and scripting the SP without current Permissions:

select "grant execute on " + name + " to db_procexecutor_write"
from sysobjects where xtype='P'

"news.microsoft.com" <joe (AT) msn (DOT) com> wrote