Re: Reset permissions
From: Clifford Dibble (CliffordDibble_at_discussions.microsoft.com)
Date: 02/03/05
- Next message: Peter Chapman: "Windows authentication without a domain"
- Previous message: Jack Yao: "Re: permissions gone missing in sysprotects"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 2 Feb 2005 22:45:02 -0800
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(@sqltemplate, '<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...
> > 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
> >
> >
>
>
- Next message: Peter Chapman: "Windows authentication without a domain"
- Previous message: Jack Yao: "Re: permissions gone missing in sysprotects"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
