SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure spGrantOrRevokePermissionToObject    Script Date: 23/11/2006 17:14:35 ******/
create procedure spGrantOrRevokePermissionToObject
@strServer Varchar(100),
@login  Varchar(100)=null,
@password Varchar(100)=null,
@ROLE_NAME varchar(100),
@DataBaseToDo Varchar(100)='%',--defaults to all databases
@ObjectType Varchar(100)='StoredProcedures',
@Privileges varchar(100)='Execute',
@GrantOrRevoke varchar(10)='Grant'
/*

--SQL Server Authentication
execute spGrantOrRevokePermissionToObject 'MyServer','MyLogin','MyPassword'
--Windows Authentication
1/ Grant Execute privilege for stored procedures in the database 'Waffle' for 'PhilFactor'
execute spGrantOrRevokePermissionToObject 'Babbage',null,null,'PhilFactor','waffle'

2/ Grant insert permission on all tables in the database 'Waffle' for 'PhilFactor'
execute spGrantOrRevokePermissionToObject 'Babbage',null,null,'PhilFactor','waffle','Tables','Insert'

3/ Revoke all privileges for stored procedures in the database 'Waffle' for 'PhilFactor'
execute spGrantOrRevokePermissionToObject 'Babbage',null,null,'PhilFactor','waffle',default,'Execute','Revoke'

4/ Revoke insert permission on all tables in the database 'Waffle' for 'PhilFactor'
execute spGrantOrRevokePermissionToObject 'Babbage',null,null,'PhilFactor','waffle','Tables','Insert','Revoke'

*/

as
declare @hr int,		--the HRESULT returned from the OLE operation
	@ii int,		--a simple counter
	@iimax int,		--the max of the iteration
	@jj int,		--another simple counter
	@jjmax int,		--the max of the iteration
	@objServer int,		--the Server object
	@ErrorObject int,	--the error object
	@ErrorMessage varchar(255),--the potential error message
	@command varchar(255),	--the command
	@DatabaseName Varchar(255),	--The String with the current Database
	@Yes int,	--Logical value returned by DMO
	@Slice int, 
	@PrivilegeBitmap int,
	@PrivilegeName varchar(100)

set nocount on

--set the privilege types
DECLARE @SQLDMOPriv_Unknown int SET @SQLDMOPriv_Unknown = 0
DECLARE @SQLDMOPriv_Select int SET @SQLDMOPriv_Select = 1
DECLARE @SQLDMOPriv_Insert int SET @SQLDMOPriv_Insert = 2
DECLARE @SQLDMOPriv_Update int SET @SQLDMOPriv_Update = 4
DECLARE @SQLDMOPriv_Delete int SET @SQLDMOPriv_Delete = 8
DECLARE @SQLDMOPriv_Execute int SET @SQLDMOPriv_Execute = 16
DECLARE @SQLDMOPriv_References int SET @SQLDMOPriv_References = 32
DECLARE @SQLDMOPriv_AllObjectPrivs int SET @SQLDMOPriv_AllObjectPrivs = 63
DECLARE @SQLDMOPriv_CreateTable int SET @SQLDMOPriv_CreateTable = 128
DECLARE @SQLDMOPriv_CreateDatabase int SET @SQLDMOPriv_CreateDatabase = 256
DECLARE @SQLDMOPriv_CreateView int SET @SQLDMOPriv_CreateView = 512
DECLARE @SQLDMOPriv_CreateProcedure int SET @SQLDMOPriv_CreateProcedure = 1024
DECLARE @SQLDMOPriv_DumpDatabase int SET @SQLDMOPriv_DumpDatabase = 2048
DECLARE @SQLDMOPriv_CreateDefault int SET @SQLDMOPriv_CreateDefault = 4096
DECLARE @SQLDMOPriv_DumpTransaction int SET @SQLDMOPriv_DumpTransaction = 8192
DECLARE @SQLDMOPriv_CreateRule int SET @SQLDMOPriv_CreateRule = 16384
DECLARE @SQLDMOPriv_DumpTable int SET @SQLDMOPriv_DumpTable = 32768
DECLARE @SQLDMOPriv_CreateFunction int SET @SQLDMOPriv_CreateFunction = 65536
DECLARE @SQLDMOPriv_AllDatabasePrivs int SET @SQLDMOPriv_AllDatabasePrivs = 130944


Select @Slice=1, @PrivilegeBitmap=0

WHILE @slice<>0 and @Privileges is not null
	BEGIN
	SELECT @slice=CHARINDEX(',',@Privileges)
	Select @PrivilegeName=case when @slice=0 then @Privileges
				else LEFT(@Privileges,@slice-1) end
	if @slice>0 
		SELECT @Privileges= STUFF(@Privileges,1,@slice,'')
	select @PrivilegeBitmap=@PrivilegeBitmap | 
		case @PrivilegeName 
			when 'select' then @SQLDMOPriv_Select
			when 'Insert' then @SQLDMOPriv_Insert
			when 'Update' then @SQLDMOPriv_Update
			when 'Delete' then @SQLDMOPriv_Delete
			when 'Execute' then @SQLDMOPriv_Execute
--naturally, extend this with the other definitions above if you want to.
--but I dont! @SQLDMOPriv_AllDatabasePrivs looks dead scarey
			else 0
		end
	END
if coalesce(@PrivilegeBitmap,0)=0
	begin
	raiserror ('Invalid permission for this operation',16,1)
	return 1
	end
if @GrantOrRevoke not in ('grant','Revoke')
	begin
	raiserror ('Operation must be grant or revoke',16,1)
	return 1
	end
if @ObjectType not in ('StoredProcedures','Tables')
	begin
	raiserror ('Object must be StoredProcedures or Tables',16,1)
	return 1
	end

Select @ErrorMessage='Creating DMO SQLServer object'
exec @hr = sp_OACreate 'SQLDMO.SQLServer2', @objServer OUT
if @password is null or @login is null
	begin
	--use a trusted connection 
	if @hr=0 Select @ErrorMessage='Setting login to windows authentication on '+@strServer,
	@ErrorObject=@objServer
	if @hr=0 exec @hr = sp_OASetProperty @objServer, 'LoginSecure', 1
	if @hr=0 Select @ErrorMessage='logging in to the requested server using windows authentication on '+@strServer
	if @login is null and @hr=0 exec @hr = sp_OAMethod @objServer, 'Connect', NULL, @strServer 
	if @login is not null and @hr=0 exec @hr = sp_OAMethod @objServer, 'Connect', NULL, @strServer ,@Login
	end
else
	Begin
	if @hr=0 SELECT @ErrorMessage = 'Connecting to '''+@strServer+
		''' with user ID '''+@login+'''', @ErrorObject=@objServer
	if @hr=0 exec @hr = sp_OAMethod @objServer, 'Connect', NULL, @strServer ,
						 @login , @password
	end
Select @errorMessage='finding the number of databases in '+@strServer,
	@ErrorObject=@objServer
if @HR=0  EXEC @hr = sp_OAGetProperty @objServer, 'databases.Count', 
	@iimax OUT
Select @errorMessage='Getting each item',@ii=1
while @hr=0 and @ii<=@iiMax
	begin
	Select @errorMessage='finding the next database in '+@strServer,
		@command='databases.item('+cast (@ii as varchar)+').name'
	EXEC @hr = sp_OAGetProperty @objServer, @command, 
		@DatabaseName OUT
	if @hr=0 and @DatabaseName like @DatabaseToDo
		begin
		select @jj=1, @errorMessage='finding the number of '+@ObjectType,
			@command='databases.item('+cast (@ii as varchar)+').'+@ObjectType+'.count'
		EXEC @hr = sp_OAGetProperty @objServer, @command, 
			@jjMax OUT
		while @hr=0 and @jj<=@jjMax
			begin
			Select @command='databases.item('+
				cast (@ii as varchar)+
				').'+@ObjectType+'('+
				cast (@jj as varchar)+
				').SystemObject',
			@errorMessage='Finding whether the '+@ObjectType+' is a SystemObject'
			EXEC @hr = sp_OAGetProperty @objServer, @command, 
				@Yes OUT
			if @hr=0 Select @errorMessage='Assigning role to '+@ObjectType,
				@command='databases.item('+cast (@ii as varchar)+
					').'+@ObjectType+'('+
					cast (@jj as varchar)+
					').'+@GrantOrRevoke
			if @hr=0 and @Yes=0 
				begin
				exec @hr = sp_OAMethod @objServer, @Command, 
				 NULL, @PrivilegeBitmap ,@ROLE_NAME
				--Select @Command
				end
			Select @jj=@jj+1
			end
	
		end
	Select @ii=@ii+1
	end
if @hr<>0
	begin
	Declare 
		@Source varchar(255),
		@Description Varchar(255),
		@Helpfile Varchar(255),
		@HelpID int
	
	EXECUTE sp_OAGetErrorInfo  @errorObject, 
		@source output,@Description output,@Helpfile output,@HelpID output

	Select @ErrorMessage='Error whilst '+@Errormessage+', '+@Description
	raiserror (@ErrorMessage,16,1)
	end
exec sp_OADestroy @objServer
return @hr


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO