Tags: based, certain, database, deny, depending, dynamically, grant, microsoft, mysql, oracle, permissions, reset, run, script, server, sql, upon, whichdatabase

Reset permissions

On Database » Microsoft SQL Server

3,144 words with 2 Comments; publish: Mon, 19 May 2008 22:35:00 GMT; (250125.00, « »)

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

All Comments

Leave a comment...

  • 2 Comments
    • sp_msForeachtable 'Grant select on ? to Public'

      Change What u want on the Statement but ?

      "jake" <rondican.sqlserver.todaysummary.com.hotmail.com> wrote in message

      news:OT%23e5$%239EHA.3260.sqlserver.todaysummary.com.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

      >

      #1; Mon, 19 May 2008 22:36:00 GMT
    • 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(.sqlserver.todaysummary.com.sqltemplate nvarchar(2000)

      , .sqlserver.todaysummary.com.gdr nvarchar(6)

      , .sqlserver.todaysummary.com.perm nvarchar(128)

      , .sqlserver.todaysummary.com.object nvarchar(128)

      , .sqlserver.todaysummary.com.user nvarchar(128)

      )

      returns nvarchar(4000) as

      begin

      declare .sqlserver.todaysummary.com.sql nvarchar(4000)

      select .sqlserver.todaysummary.com.sql = replace(replace(replace(replace(.sqlserver.todaysummary.com.sqltemp

      late, '<gdr>',

      .sqlserver.todaysummary.com.gdr), '<perm>', .sqlserver.todaysummary.com.perm), '<object>', .sqlserver.todaysummary.com.object), '<user>', .sqlserver.todaysummary.com.user)

      return .sqlserver.todaysummary.com.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.sqlserver.todaysummary.com.hotmail.com> wrote in message

      > news:OT%23e5$%239EHA.3260.sqlserver.todaysummary.com.TK2MSFTNGP14.phx.gbl...

      > which

      > group

      >

      #2; Mon, 19 May 2008 22:37:00 GMT