Reset permissions

Hello,
I am looking for a script that will work dynamically depending on which
database I am in. I want to run the GRANT or DENY based upon a certain group
and I would prefer not to select each table the EM. Is there a script out
there that can read the tables and generate a permission script off of it?
Thanks in advance.
Jakesp_msForeachtable 'Grant select on ? to Public'
Change What u want on the Statement but ?
"jake" <rondican@.hotmail.com> wrote in message
news:OT%23e5$%239EHA.3260@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I am looking for a script that will work dynamically depending on
which
> database I am in. I want to run the GRANT or DENY based upon a certain
group
> and I would prefer not to select each table the EM. Is there a script out
> there that can read the tables and generate a permission script off of it?
> Thanks in advance.
> Jake
>|||Hi,
I do not recommend 'sp_msForeachtable' because it is undocumented. I
strongly urge all of you to think long and hard before embedding calls to
undocumented APIs in production code. Some of these undocumented APIs are
gone in Yukon.
I recommend building some simple code generators like the one shown below.
FYI, in Yukon you can GRANT/DENY/REVOKE permissions at different scopes. So
you can do this:
-- GRANT EXECUTE on all current and future procs and scalar funcs
-- in schema
--
GRANT EXECUTE ON SCHEMA :: someschema TO someuser
-- GRANT EXECUTE on all current and future procs and scalar funcs
-- in all schemas in the current database
--
GRANT EXECUTE TO someuser
Regards,
Clifford Dibble
Program Manager
SQL Server Engine
create function make_sql(@.sqltemplate nvarchar(2000)
, @.gdr nvarchar(6)
, @.perm nvarchar(128)
, @.object nvarchar(128)
, @.user nvarchar(128)
)
returns nvarchar(4000) as
begin
declare @.sql nvarchar(4000)
select @.sql = replace(replace(replace(replace(@.sqltemp
late, '<gdr>',
@.gdr), '<perm>', @.perm), '<object>', @.object), '<user>', @.user)
return @.sql
end
go
select dbo.make_sql('<gdr> <perm> ON <object> TO <user>', 'GRANT', 'SELECT',
o.name, 'PUBLIC')
from sysobjects as o
where o.type = 'U'
go
select dbo.make_sql('<gdr> <perm> ON <object> TO <user>', 'REVOKE',
'INSERT', o.name, 'PUBLIC')
from sysobjects as o
where o.type = 'U'
go
"Melih SARICA" wrote:

> sp_msForeachtable 'Grant select on ? to Public'
> Change What u want on the Statement but ?
> "jake" <rondican@.hotmail.com> wrote in message
> news:OT%23e5$%239EHA.3260@.TK2MSFTNGP14.phx.gbl...
> which
> group
>sql