This one puts the output into SQL commands that you can cut and paste into a new query and execute.
-- af_revPermissions
-- Verify existence of af_revPermissions procedure. If it does exist,
-- drop it, so that it can be re-created.
if exists (select * from sysobjects where id = object_id(N'[dbo].[af_revPermissions]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
print 'dropping existing af_revPermissions procedure.'
drop procedure [dbo].[af_revPermissions]
print 'creating af_revPermissions procedure.'
end
GO
create procedure af_revPermissions
@login_name sysname = null,
@db_parm sysname = null
as
/*************************************************************/
--
-- Module Name: af_revPermissions
--
-- Description:
-- This procedure accepts two parameters, @login_name and @db_parm.
-- If a login_name is passed into the procedure, execution will
-- be for that login_name only. Otherwise, the procedure
-- will execute for all login_names.
-- If a database name is passed in @db_parm, execution will be
-- for that database only. Otherwise, all databases on the server
-- will be processed.
-- The purpose of this procedure is to provide 'grant' and
-- 'deny' statements that can be used to reapply permissions
-- in the given database.
--
-- Written By: Steve Phelps
--
-- Date: July 15, 2003
--
-- Modified : Steve Phelps
-- Date: Sep 12, 2003
-- Modified the @column edits to include a null value.
--
-- USAGE:
--
-- use pubs
-- declare
-- @login_name sysname,
-- @db_parm sysname
-- set @login_name = 'progsbp'
-- set @db_parm = 'pubs'
-- exec af_revPermissions
-- @login_name,
-- @db_parm
--
/*************************************************************/
-- the following declare and set statements are for test purposes.
--
-- declare @login_name sysname,
-- @db_parm sysname
-- set @login_name = null
-- --set @login_name = 'netiq'
-- -- set @db_parm = null
-- set @db_parm = 'AXENT_Defender'
--
SET NOCOUNT ON
declare @command varchar(5000),
@limit int,
@counter int,
@object varchar(128),
@owner varchar(50),
@type char(2),
@grantee varchar(50),
@protect_type varchar(6),
@action varchar(10),
@column varchar (50),
@id int,
@num int,
@date datetime,
@timestamp varchar(50),
@tim varchar(10),
@db_name varchar(50),
@return_code int,
@db_fetch_status int,
@objects_fetch_status int,
@permissions_fetch_status int,
@flag int
set @limit = 0
set @counter = 0
set @date = getdate()
select @timestamp = convert(char(8),@date,112)
select @tim-2 = convert(char(10),@date,108)
select @tim-2 = replace(@tim,':','')
select @timestamp = @timestamp + rtrim(@tim)
--temporary table to identify objects to take over...
Create Table #Hold_Objects
( #Object_Name varchar(128),
#Type char(2),
#Owner_UID int,
#Owner_Name varchar(50))
-- temporary table to hold permissions for the object
Create Table #Permissions_Table
( #id int identity,
#Owner varchar(50),
#Object varchar(128),
#Grantee varchar(50),
#Grantor varchar(50),
#Protect_Type varchar(10),
#Action varchar(50),
#Column varchar(50))
create table #db_Table
(#db_Name sysname)
if @db_parm is null
insert #db_Table
select name
from master..sysdatabases
where name not in ('tempdb', 'model', 'pubs', 'Northwind')
else
insert #db_Table
select @db_parm
declare db_cur cursor for
select #db_Name
from #db_Table
order by #db_Name
open db_cur
fetch next from db_cur into
@db_name
set @db_fetch_status = @@fetch_status
if (@db_fetch_status = -1)
begin
close db_cur
deallocate db_cur
-- return -1
end
else
begin
while (@db_fetch_status <> -1)
begin
-- begin processing current db.
print '-- Permissions for database = ' + @db_name
-- Check database properties to ensure successful processing.
set @flag = 0
-- if (select databaseproperty(@db_name, 'isemergencymode')) = '1'
-- begin
-- print '-- * emergency mode is ON. *'
-- set @flag = @flag + 1
-- end
if (select databaseproperty(@db_name, 'isInLoad')) = '1'
begin
print '-- * database is in LOAD. *'
set @flag = @flag + 1
end
if (select databaseproperty(@db_name, 'isInRecovery')) = '1'
begin
print '-- * database is in RECOVERY. *'
set @flag = @flag + 1
end
if (select databaseproperty(@db_name, 'isInStandBy')) = '1'
begin
print '-- * database is in STANDBY. *'
set @flag = @flag + 1
end
if (select databaseproperty(@db_name, 'isNotRecovered')) = '1'
begin
print '-- * database has FAILED RECOVERY. *'
set @flag = @flag + 1
end
if (select databaseproperty(@db_name, 'isOffLine')) = '1'
begin
print '-- * database is OFFLINE. *'
set @flag = @flag + 1
end
if (select databaseproperty(@db_name, 'isShutDown')) = '1'
begin
print '-- * database is SHUTDOWN. *'
set @flag = @flag + 1
end
if (select databaseproperty(@db_name, 'isSuspect')) = '1'
begin
print '-- * database is SUSPECT. *'
set @flag = @flag + 1
end
if @flag > 0
begin
print ' '
print '-- Because of the above described database condition(s),'
print '-- this database will not be processed. Skipping to'
print '-- next database.'
set @db_fetch_status = -2
end
-- end of new database check code.
if (@db_fetch_status <> -2)
begin
-- gather list of objects (tables, views and procedures).
select @command = '
use ' + @db_name + '
set nocount on
select so.name, so.type, so.uid, su.name
from ' + @db_name + '..sysobjects so
inner join ' + @db_name + '..sysusers su
on so.uid = su.uid
where so.type in (''U'', ''V'', ''P'', ''X'', ''FN'', ''IF'', ''TF'')'
insert #Hold_Objects
exec (@command)
print '/*'
Declare objects_cur cursor for
select ho.#Object_Name,
ho.#Type,
ho.#Owner_Name
from #Hold_Objects ho
order by ho.#Object_Name,
ho.#Owner_Name
open objects_cur
fetch next from objects_cur into
@object, @type, @owner
set @objects_fetch_status = @@fetch_status
if (@objects_fetch_status = -1)
begin
close objects_cur
deallocate objects_cur
-- return -1
end
while (@objects_fetch_status <> -1)
begin
if (@objects_fetch_status <> -2)
begin
print 'Checking: ' + @object
-- build command to gather permissions for current object and place them in #Permissions_Table
select @command = 'use ' + @db_name + ' exec sp_af_helprotect ' + char(39) + @object + char(39)
insert #Permissions_Table (#Owner, #Object, #Grantee, #Grantor,
#Protect_Type, #Action, #Column)
exec (@command)
end -- if (@objects_fetch_status <> -2)
fetch next from objects_cur into
@object, @type, @owner
set @objects_fetch_status = @@fetch_status
end -- while (@objects_fetch_status <> -1
close objects_cur
deallocate objects_cur
-- begin loop through permissions table to reapply permissions.
print '*/'
Declare permissions_cur cursor for
select
pt.#id,
pt.#Owner,
pt.#Object,
pt.#Grantee,
pt.#Protect_Type,
pt.#Action,
pt.#Column,
ho.#Type
from #Permissions_Table pt
inner join #Hold_Objects ho
on pt.#Object = ho.#Object_Name
-- where pt.#Grantee <> 'public'
order by pt.#Object Asc, pt.#Grantee Asc
open permissions_cur
fetch next from permissions_cur into
@id, @owner, @object, @grantee, @protect_type, @action, @column, @type
set @permissions_fetch_status = @@fetch_status
if (@permissions_fetch_status = -1)
begin
close permissions_cur
deallocate permissions_cur
-- return -1
end
else
begin
select @command = 'use ' + @db_name
print @command
while (@permissions_fetch_status <> -1)
begin
if (@permissions_fetch_status <> -2)
if @login_name is null or
@grantee = @login_name
begin
-- build statement to reapply the permissions for the current record.
-- check for all possibilities
-- the following line modified 9/12/03 sbp
if @column = '.' or @column = '(All+New)' or @column = '(All)' or @column is null
set @column = ' '
else
set @column = ' ([' + @column + ']) '
select @command = @protect_type + ' ' + @action +
' on ' + '[' + @db_name + ']' + '.' + @owner + '.' + '[' + @object + ']' + @column +
' to ' + '[' + @grantee + ']'
if @protect_type = 'deny'
set @command = @command + ' cascade'
print @command
select @counter = @counter + 1
end -- if @login_name is null or...
fetch next from permissions_cur into
@id, @owner, @object, @grantee, @protect_type, @action, @column, @type
set @permissions_fetch_status = @@fetch_status
end -- while (@permissions_fetch_status <> -1)
close permissions_cur
deallocate permissions_cur
end -- (@permissions_fetch_status = -1)
if @counter = 0
if @login_name is not null
print '-- ' + @login_name + ' has no object level permissions assigned in this database.'
else
print '-- There are no object level permissions assigned in this database.'
end -- if (@db_fetch_status <> -2)
truncate table #Hold_Objects
truncate table #Permissions_Table
print 'GO'
print '-- End of processing for Database = ' + @db_name
print '-- -------------------------------------------------------------------------------------'
fetch next from db_cur into
@db_name
set @db_fetch_status = @@fetch_status
end -- while (@db_fetch_status <> -1)
close db_cur
deallocate db_cur
end -- (@db_fetch_status = -1)
-- cleanup
drop table #Hold_Objects
drop table #Permissions_Table
drop table #db_Table
GO