Grant access to Public for all user objects

,

Grant access to Public for all user objects (tables, views, stored procedures).

CREATE PROCEDURE  sp_InitPrivileges AS

-- Notes: This procedure grants read access to public for all user tables, views and stored procedures

DECLARE @UserName varchar(30)
set @UserName =  'public'

DECLARE @ExecStr varchar(255)
DECLARE @SYSTABLETYPE char(1)
set @SysTableType = 'S'

DECLARE objects_cursor CURSOR
    FOR
    SELECT Name, Type FROM sysobjects WHERE type in('U','V','P') and uid = 1

OPEN objects_cursor
DECLARE @name sysname
DECLARE @type char(10)

FETCH NEXT FROM objects_cursor INTO @name, @type
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    -- Print "Object:" +@name + " " + convert(varchar(30),@type)

if @type = 'U'  -- User Table
begin
SELECT @ExecStr = 'grant SELECT on [' + @name + '] to ' + @UserName
-- SELECT @EXecStr
EXEC (@ExecStr)
End
if @type = 'P'-- Stored Procedure
Begin
SET @ExecStr = 'grant EXEC on [' + @name + '] to ' + @UserName 
--PRINT @EXecStr
EXEC (@ExecStr)
end
if @type = 'V'
begin
SET @ExecStr = 'grant SELECT on [' + @name + '] to ' + @UserName
--PRINT @type + " " + @EXecStr
EXEC (@ExecStr)
end

    FETCH NEXT FROM objects_cursor INTO @name, @type
END

DEALLOCATE objects_cursor
GO

Rate

5 (1)

Share

Share

Rate

5 (1)