December 12, 2007 at 11:28 am
OK, I am not a *real* DBA (I just play one at work ... :)). I am a programmer who happens to be administering a database. So .. if this is a dumb question, please keep that in mind!
I am using SQL 2005, and I am wondering if there is a way to set up a role such that when a new stored procedure is created, the role will automatically be given "execute" permission on that stored procedure.
We have our own dedicated server, but I also administer a database on a shared server (on DiscountASP.net), and they have it set up that way -- when a new stored procedure is created, the user set up by DiscountASP automatically has EXECUTE permission on that procedure.
I keep trying to duplicate this on our dedicated server, but to no avail. I would like to make it so a specific role always has execute permission on every new stored procedure, without having to explicitly add permissions to the role using the properties of the stored procedure.
Is this possible? It seems like it should be, but I can't figure out how.
-- Cynthia
December 12, 2007 at 12:08 pm
Sure it is 🙂
1) Create a new Database role for your users, for example 'PowerUsers'
2) Create a trigger to grant EXEC and VIEW DEFINITION for your new procedures.
CREATE TRIGGER TR_SP_CONVENTIONS
ON DATABASE
FOR CREATE PROCEDURE, ALTER PROCEDURE
AS
declare @new_schema_name nvarchar(250)
declare @new_obj_name nvarchar(250)
select @new_schema_name = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(250)')
select @new_obj_name = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(250)')
declare @sql1 nvarchar(max)
set @sql1 = 'grant exec on ' + @new_schema_name + '.' + @new_obj_name + ' to [PowerUsers]'
exec sp_executesql @sql1
declare @sql2 nvarchar(max)
set @sql2 = 'grant view definition on ' + @new_schema_name + '.' + @new_obj_name + ' to [PowerUsers]'
exec sp_executesql @sql2
GO
Tommy
Follow @sqlscribeDecember 12, 2007 at 1:39 pm
OK, when I try to run this, it's telling me: Must declare the scalar variable "@data".
What is "@data" supposed to be? (Remember, I am really ignorant here!)
December 12, 2007 at 2:28 pm
There are two ways I know of to accomplish this.
1. Grant permission at the schema level. If all stored procedures are in the same schema, like dbo, you can grant the EXECUTE permisson on applicable objects in that schema:
/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE ON SCHEMA::dbo TO db_executor
2. Grant permission at the database level.
/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
I've read a post by Brian Kelley recently that says securing at the schema level is preferable to securing at the database level.
Greg
Greg
December 12, 2007 at 2:38 pm
😉
Try
CREATE TRIGGER TR_SP_CONVENTIONS
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE
AS
declare @data XML
declare @new_schema_name nvarchar(250)
declare @new_obj_name nvarchar(250)
set @data = EVENTDATA()
select @new_schema_name = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(250)')
select @new_obj_name = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(250)')
declare @sql1 nvarchar(max)
set @sql1 = 'grant exec on ' + @new_schema_name + '.' + @new_obj_name + ' to [PowerUsers]'
exec sp_executesql @sql1
declare @sql2 nvarchar(max)
set @sql2 = 'grant view definition on ' + @new_schema_name + '.' + @new_obj_name + ' to [PowerUsers]'
exec sp_executesql @sql2
GO
Tommy
Follow @sqlscribeDecember 12, 2007 at 2:52 pm
Yes, this seems to work Greg! Thanks very much.
Thanks for the update on the trigger, Tommy, but Greg's solution seems to be easier :)!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply