All Object Security Grant

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_All_Object_Security_Grant]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_All_Object_Security_Grant]
GO

CREATE PROCEDURE sp_All_Object_Security_Grant
( 

   @SQL_USER varchar(20)

)
AS

-- Copywrite 2006
-- Marc Callahan
-- egal4all@gmail.com
-- 7015 NW 77TH STREET
-- KANSAS CITY, MO 64152

-- CREATED BY: MARC CALLAHAN
-- DATE: 03/14/2006
-- COMMENTS:
-- PROCEDURE GRANTS PERMISSION TO A GIVEN SQL USER
-- TO ALL OBJECTS WITHIN A GIVEN CATALOG CONTEXT
-- STORED PROCEDURES, VIEWS, TABLES, SELECT FUNCTIONS, EXECUTE FUNCTIONS
-- MODIFY AS REQUESTED

-- SAMPLE EXECUTE 
--      EXECUTE  sp_All_Object_Security_Grant 'WUA'


DECLARE @SQL varchar(5000)
DECLARE @DB varchar(250)
DECLARE @ID int


-- CHECK IF USER IS ADDED TO CURRENT CATALOG
-- IF NOT ADD USER FROM SERVER ACCESS GRANT

if not exists (select * from dbo.sysusers where name = N'WUA')
BEGIN
   PRINT 'ADD USER'   
   exec sp_addUser @SQL_USER
END
ELSE
BEGIN
   exec sp_DropUser @SQL_USER
   exec sp_addUser @SQL_USER
END

-- STORED PROCEDURE OBJECTS
DECLARE TablesCursor CURSOR
FAST_FORWARD
FOR 
(

select [Name] from dbo.sysobjects 
where  OBJECTPROPERTY(id, N'IsProcedure')IN (1)
AND NAME NOT LIKE 'dt%'



)
OPEN TablesCursor
FETCH NEXT FROM TablesCursor INTO @DB
WHILE (@@fetch_status<>-1)
BEGIN -- WHILE BEGIN
IF (@@fetch_status<>-2)
BEGIN --IF BEGIN
SET @SQL=


'

GRANT EXECUTE ON '+@DB+' TO '+@SQL_USER+'

'
EXECUTE(@SQL)
END -- IF END

FETCH NEXT FROM TablesCursor INTO @DB
END -- WHILE END
CLOSE TablesCursor
DEALLOCATE TablesCursor
SET NOCOUNT OFF



-- TABLE OBJECTS


DECLARE TablesCursor CURSOR
FAST_FORWARD
FOR 
(


select [Name] from dbo.sysobjects 
where  OBJECTPROPERTY(id, N'IsTable')IN (1)
AND [Name] NOT LIKE 'dt%'
AND [Name] NOT LIKE 'sys%'



)
OPEN TablesCursor
FETCH NEXT FROM TablesCursor INTO @DB
WHILE (@@fetch_status<>-1)
BEGIN -- WHILE BEGIN
IF (@@fetch_status<>-2)
BEGIN --IF BEGIN
SET @SQL=


'

GRANT SELECT, INSERT, UPDATE, DELETE ON '+@DB+' TO WUA


'
EXECUTE(@SQL)
END -- IF END

FETCH NEXT FROM TablesCursor INTO @DB
END -- WHILE END
CLOSE TablesCursor
DEALLOCATE TablesCursor
SET NOCOUNT OFF



-- VIEW OBJECTS

DECLARE TablesCursor CURSOR
FAST_FORWARD
FOR 
(


select [Name] from dbo.sysobjects 
where  OBJECTPROPERTY(id, N'IsView')IN (1)
AND [Name] NOT LIKE 'dt%'
AND [Name] NOT LIKE 'sys%'



)
OPEN TablesCursor
FETCH NEXT FROM TablesCursor INTO @DB
WHILE (@@fetch_status<>-1)
BEGIN -- WHILE BEGIN
IF (@@fetch_status<>-2)
BEGIN --IF BEGIN
SET @SQL=


'

GRANT SELECT, INSERT, UPDATE, DELETE ON '+@DB+' TO WUA


'
EXECUTE(@SQL)
END -- IF END

FETCH NEXT FROM TablesCursor INTO @DB
END -- WHILE END
CLOSE TablesCursor
DEALLOCATE TablesCursor
SET NOCOUNT OFF



-- EXECUTE FUNCTION OBJECTS


DECLARE TablesCursor CURSOR
FAST_FORWARD
FOR 
(



select [Name] 
from dbo.sysobjects 
where xtype in (N'FN')


)
OPEN TablesCursor
FETCH NEXT FROM TablesCursor INTO @DB
WHILE (@@fetch_status<>-1)
BEGIN -- WHILE BEGIN
IF (@@fetch_status<>-2)
BEGIN --IF BEGIN
SET @SQL=


'

GRANT EXECUTE ON '+@DB+' TO WUA


'
EXECUTE(@SQL)
END -- IF END

FETCH NEXT FROM TablesCursor INTO @DB
END -- WHILE END
CLOSE TablesCursor
DEALLOCATE TablesCursor
SET NOCOUNT OFF



-- SELECT FUNCTION OBJECTS

DECLARE TablesCursor CURSOR
FAST_FORWARD
FOR 
(



select [Name] 
from dbo.sysobjects 
where xtype in (N'IF', N'TF')


)
OPEN TablesCursor
FETCH NEXT FROM TablesCursor INTO @DB
WHILE (@@fetch_status<>-1)
BEGIN -- WHILE BEGIN
IF (@@fetch_status<>-2)
BEGIN --IF BEGIN
SET @SQL=


'

GRANT SELECT ON '+@DB+' TO WUA


'
EXECUTE(@SQL)
END -- IF END

FETCH NEXT FROM TablesCursor INTO @DB
END -- WHILE END
CLOSE TablesCursor
DEALLOCATE TablesCursor
SET NOCOUNT OFF



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO