[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

Is there any way to create a script to grant permissions to an object such as a stored procedure or table after the object has been created?

Basically going forward, is there a script that can be ran to grant execute rights to any stored procedure that is created without having to do it in each sp one by one.

For example:  I just created two stored procedures, now I would like to grant permission on those two after the fact.  One though is loop through the sysobjects table that is left joined by the syspermissions table;  gives me the list of stored procedures that currently do not have grant execute on and finally grant execute on those sp's.

The t-sql can look something like this:

select o.name as sp_name
from syspermissions p
right join sysobjects o
on o.id = p.id
where o.xtype = 'P'
and o.name not like 'dt%'
and p.grantee IS NULL

Any suggestions?

0
ktt2
Asked:
ktt2
  • 2
1 Solution
 
napel25Commented:
Two options I use most:

select 'grant execute on ' + [name] + ' to userx'
from sysobjects
where xtype = 'P'
and crdate > datediff(hh, -1, getdate())

select 'grant execute on ' + o.[name] + ' to userx' as statement
from syspermissions p
right join sysobjects o
on o.id = p.id
where o.xtype = 'P'
and o.name not like 'dt%'
and p.grantee IS NULL

0
 
napel25Commented:
Copy and paste the result in the query field and execute it again.

The second option is a merge of your and my way ofcourse... ;)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now