Roles - 02-15-2005 , 06:29 PM
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:
GRANT EXECUTE ON dbo.p_CnsAct_i TO WINADMIN
GRANT EXECUTE ON dbo.p_CnsAct_i TO WINCNS
GRANT EXECUTE ON dbo.p_CnsAct_i TO WINCNSSUPV
GRANT EXECUTE ON dbo.p_CnsAct_i TO WINCOMPILEALL
When I'm done - it should only have:
GRANT EXECUTE ON db_procexecutor_write
Re: Roles - 02-15-2005 , 07:00 PM
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
Re: Roles - 02-15-2005 , 07:10 PM
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
Re: Roles - 02-15-2005 , 09:07 PM
-- Create a test proc
create proc dbo.sec_test
print 'security test'
-- create a new server role
Exec sp_addrole 'TEST_ROLE'
-- grant execute on the proc to the new rolw
grant execute on dbo.sec_test to TEST_ROLE
-- 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
select id, object_name(id) as "object_name"
where grantee = 16400
and ObjectProperty(id, 'IsProcedure') = 1
Select * from #procs
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.
Re: Roles - 02-16-2005 , 09:38 AM
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