Setting permissions (fix for no dbo users)

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE  PROCEDURE spSetPermissionsGlobally(@name nvarchar(128) = 'public', 
@printonly bit = 1,
@revokeOldRights as bit = 1,
@processViews bit = 1,
@processProcs bit = 1,
@processTables bit = 0) AS

SET NOCOUNT ON

DECLARE permissions_cursor CURSOR FOR 
SELECT u.[name] + '.' + s.[name], 
s.xtype
FROM sysobjects s, sysusers u 
WHERE ((OBJECTPROPERTY(s.id, N'IsView') = 1 AND @processViews = 1) -- <--Stored Procs, Tables, and Views, OH MY!
OR (OBJECTPROPERTY(s.id, N'IsProcedure') = 1 AND @processProcs = 1)
OR (OBJECTPROPERTY(s.id, N'IsTable') = 1 AND @processTables = 1))
AND  OBJECTPROPERTY(s.id, N'IsMSShipped') = 0 -- <--ask for any object that did not come with SQL Server
AND s.[name] <> 'spSetPermissionsGlobally'
AND s.uid = u.uid
ORDER BY s.xtype, 
s.[name] -- <--makes it run slower, but easier to find items in the QA output window(of course there is CTRL+F :P )

DECLARE @objname nvarchar(128),
@type char(1),
@sql varchar(200), 
@sqlrevoke varchar(200)

OPEN permissions_cursor

FETCH NEXT FROM permissions_cursor 
INTO @objname, @type

WHILE @@FETCH_STATUS = 0
BEGIN
IF(LOWER(@objname) <> LOWER('spSetPermissionsGlobally'))
BEGIN
IF(@type = 'V') -- VIEW
SET @sql = 'GRANT  SELECT  ON ' + @objname + ' TO ' + @name

IF(@type = 'P') -- STORED PROC
SET @sql = 'GRANT  EXECUTE  ON ' + @objname + ' TO ' + @name

IF(@type = 'U') -- TABLE
SET @sql = 'GRANT  SELECT, UPDATE, INSERT, DELETE ON ' + @objname + ' TO ' + @name

SET @sqlrevoke = 'REVOKE ALL ON ' + @objname + ' TO ' + @name

IF @printonly = 0
PRINT '--*****Setting permissions for : ' + @objname + '*****'
ELSE
PRINT 'PRINT ''*****Setting permissions for : ' + @objname + '*****'''

IF(@revokeOldRights = 1) --revoke the old rights?
BEGIN
PRINT @sqlrevoke
IF @printonly = 0
EXEC (@sqlrevoke)
ELSE
PRINT 'GO'
END

PRINT @sql 
IF @printonly = 0
EXEC (@sql)
ELSE
PRINT 'GO'

PRINT ''
END

FETCH NEXT FROM permissions_cursor 
INTO @objname, @type
END

CLOSE permissions_cursor
DEALLOCATE permissions_cursor


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO