First of all, props to David Musgrave, who does some very cools stuff. I found the main part of this script on his site, and I've altered it for my purposes. This particular script runs a 'Nolan Grant' procedure against all GP databases, but you can easily alter it to suit your needs. I copy the script here because you never know when someone else will pull down a page, this code is too cool to lose.
Dave's original article:
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/11/03/running-sql-commands-against-all-gp-company-databases.aspx
My adaptation:
exec sp_MSforeachdb
' use ?
print ''evaluating '' + db_name()
if exists ( select INTERID from DYNAMICS..SY01500 D where INTERID = ''?'' )
begin
/* this ia a Great Plains company db */
print ''Processing database ?''
declare @cStatement varchar(255)
/* this ia a Great Plains company db */
declare G_cursor CURSOR for
select ''grant select,update,insert,delete on ['' + convert(varchar(128),name) + ''] to DYNGRP'' from sysobjects
where (type = ''U'' or type = ''V'') and uid = 1
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
--print @cStatement
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
declare G_cursor CURSOR for
select ''grant execute on ['' + convert(varchar(128),name) + ''] to DYNGRP'' from sysobjects
where type = ''P''
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
--print @cStatement
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
end
'