SQL Server Security: The db_executor Role

• Olga B

  SSCommitted

  Points: 1529

  More actions

  July 25, 2006 at 4:22 pm

  #651497

  Melvin, that's an excellent idea that will be very helpful to me. One question, though. Why not assign all permissions through one cursor, like so?

  DECLARE cursAllExecutables CURSOR FAST_FORWARD

  FOR

  SELECT USER_NAME(uid) Owner, [name] StoredProcedure

  FROM sysobjects

  where xtype in ('P', 'TF', 'FN')

  Seemed to work here.

• Brendan Kerry

  SSC-Addicted

  Points: 471

  More actions

  January 21, 2007 at 7:27 pm

  #684587

  Excellent article and discussion, what would I do without sqlservercentral?

  It occurred to me that with SQL 2005 you could combine it with a ddl trigger and eliminate the need for scheduling and the sql agent altogether:

  create trigger tdGrantExecute

  on database for

  create_procedure, create_function

  as

  begin

   

   declare @exe varchar(128)

   declare @sql varchar(1000)

   declare cExe cursor forward_only for

    select objects.name

    from sys.objects

    inner join sys.schemas on objects.schema_id = schemas.schema_id

    where schemas.name = 'dbo'

     and type in ('P', 'FN', 'FS','AF','PC')

   open cExe

   fetch next from cExe into @exe

   while @@fetch_status = 0

   begin

    set @sql = 'GRANT EXECUTE ON dbo.[' + @exe + '] TO db_executor'

    exec (@sql)

    fetch next from cExe into @exe

   end

   close cExe

   deallocate cExe

  /*

  ** SQL 2005 Object Types **

  AF = Aggregate function (CLR)

  C = CHECK constraint

  D = DEFAULT (constraint or stand-alone)

  F = FOREIGN KEY constraint

  PK = PRIMARY KEY constraint

  P = SQL stored Procedure

  PC = Assembly (CLR) stored Procedure

  FN = SQL scalar function

  FS = Assembly (CLR) scalar function

  FT = Assembly (CLR) table-valued function

  R = Rule (old-style, stand-alone)

  RF = Replication-filter-Procedure

  SN = Synonym

  SQ = Service queue

  TA = Assembly (CLR) DML trigger

  TR = SQL DML trigger

  IF = SQL inlined table-valued function

  TF = SQL table-valued-function

  U = Table (user-defined)

  UQ = UNIQUE constraint

  V = View

  X = Extended stored Procedure

  IT = Internal table

  */

  end

  Note that in my environment I've restricted access to only the dbo schema.

  Thanks again!

• sho-467341

  Ten Centuries

  Points: 1062

  More actions

  July 13, 2009 at 11:05 am

  #1024054

  hi, this may be a stupid question, so apologies in advance (i'm not a dba!)... does this solution work if the users are not members of the db_datawriter role?

  for example:

  if I have a stored procedure that does an INSERT and user executing the stored procedure is a member of the user created "db_executer" role but not of the "db_datawriter" role would the user still be able run the stored procedure so that it inserts data into a table?

  cheers

• K. Brian Kelley

  SSC Guru

  Points: 114642

  More actions

  July 13, 2009 at 11:24 am

  #1024063

  Yes, if you have taken advantage of ownership chaining.

  K. Brian Kelley
  @kbriankelley

• sho-467341

  Ten Centuries

  Points: 1062

  More actions

  July 13, 2009 at 11:27 am

  #1024065

  wow - quick response 🙂

  thanks!

  think i'll have to look into "ownership chaining" but if you have any good links to this feel free to pass them on.

  cheers brian!

• K. Brian Kelley

  SSC Guru

  Points: 114642

  More actions

  July 14, 2009 at 12:50 pm

  #1024640

  sho (7/13/2009)

  ---

  wow - quick response 🙂

  thanks!

  think i'll have to look into "ownership chaining" but if you have any good links to this feel free to pass them on.

  cheers brian!

  An intro on ownership chaining in 2005/2008:

  Ownership chaining in SQL Server security feature or security risk

  K. Brian Kelley
  @kbriankelley