Download Script - View Permissions Assigned to Public Role
set quoted_identifier off

exec sp_msforeachdb @Command1="select '?' as DBName, count(*) as PublicPermissionCount from ?..sysprotects P inner join ?..sysusers U on P.Uid = U.UID inner join ?..sysobjects O on P.ID=O.ID where P.uid=0 and o.Type<>'S' and o.Name not like 'sys%' and O.Name not like 'sync%'

Download Script - Move Permissions to Everyone Role

create Proc usp_DBA_MovePublicToEveryone @EveryoneRoleName varchar(100) as 

--2/16/01 law
--Creates an 'EVERYONE' role with the same permissions that are assigned to Public,
--then adds all current users in the database to the EVERYONE role, then revokes all
--permissions from Public

Declare @RevokeSQL varchar(1000)
Declare @GrantSQL varchar(1000)

set nocount on

--default to Everyone
if @EveryoneRoleName is null or rtrim(@EveryoneRoleName)=''
set @EveryoneRoleName='EVERYONE'

--if the role already exists, warn user and exit. Otherwise we create it!
if exists (select * from dbo.sysusers where name = @EveryoneRoleName and uid > 16399)
begin
print 'Role ' + @EveryoneRoleName + ' already exists. You should remove this role or select a different name for your ''Everyone'' role'
return
end
else
EXEC sp_addrole @EveryoneRoleName


--now we need to add all existing users to the everyone role
DECLARE cur CURSOR
READ_ONLY
FOR Select name from sysusers where hasdbaccess=1 and name<>'dbo'

DECLARE @UserName as SysName
OPEN cur

FETCH NEXT FROM cur INTO @UserName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
exec sp_addrolemember @EveryoneRoleName, @UserName
END
FETCH NEXT FROM cur INTO @UserName
END

CLOSE cur
DEALLOCATE cur

--next we need to get a list of all the permissions currently granted to Public.
--Im using a temp table just to make things clear, you could use it directly in
--the cursor that follows
select P.ID, U.Name as UserName, o.name as ObjectName, 
case P.ProtectType
when 204 then 'GRANT_W_GRANT'
when 205 then 'GRANT'
when 206 then 'REVOKE'
end as ProtectType,
case p.action
when 26 then 'REFERENCES'
when 178 then 'CREATE FUNCTION'
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 198 then 'CREATE TABLE'
when 203 then 'CREATE DATABASE'
when 207 then 'CREATE VIEW'
when 222 then 'CREATE PROCEDURE'
when 224 then 'EXECUTE'
when 228 then 'BACKUP DATABASE'
when 233 then 'CREATE DEFAULT'
when 235 then 'BACKUP LOG'
when 236 then 'CREATE RULE'
end as PermissionGranted
into #Temp
from sysprotects P inner join sysusers U on P.Uid = U.UID inner join sysobjects O on P.ID=O.ID where P.uid=0 and o.Type<>'S' and O.Name not like 'sync%'
order by UserName, ObjectName

--now we apply the permissions that are in the #Temp table to the new EveryoneRole
--and at the same time revoke those same permissions from Public
DECLARE cur CURSOR
READ_ONLY
FOR Select UserName, PermissionGranted, ObjectName from #Temp

DECLARE @name varchar(40)
DECLARE @ProtectType varchar(40)
DECLARE @ObjectName varchar(40)
OPEN cur

FETCH NEXT FROM cur INTO @name, @ProtectType, @ObjectName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
--create has a slightly different syntax, so we have to branch
--here
if @ProtectType like 'Create%'
begin
set @GrantSQL ='Grant ' + @ProtectType + ' to ' + @EveryoneRoleName
print @GrantSQL
exec(@GrantSQL)
set @RevokeSQL ='Revoke ' + @ProtectType + ' on [' + @ObjectName + '] from ' + @Name
print @RevokeSQL
exec(@RevokeSQL)
end
else
begin
set @GrantSQL ='Grant ' + @ProtectType + ' on [' + @ObjectName + '] to ' + @EveryoneRoleName
print @GrantSQL
exec(@GrantSQL)
set @RevokeSQL ='Revoke ' + @ProtectType + ' on [' + @ObjectName + '] from ' + @Name
print @RevokeSQL
exec(@RevokeSQL)
end
END
FETCH NEXT FROM cur INTO @name, @ProtectType, @ObjectName
END

CLOSE cur
DEALLOCATE cur

--clean up the working table
drop table #Temp

print 'Permissions have been moved from Public to ' + @EveryoneRoleName