granting create proc / func to create dbo objects
-
xpost from the sql7/sql2000 forum 
But the question is the same for SQL2005
Still trying to figure out if I can grant create procedure to a user so (s)he can create / alter / drop dbo owned userprocedures without that user being db_owner ?
-- logged on as alzdba (db_owner)
grant CREATE PROCEDURE TO alzuser ;
-- logged on as user alzuser (db_datareader)
create PROC dbo.[spc_1]
AS
BEGIN
SET NOCOUNT ON
SELECT *
FROM T1
END
--
Msg 2760
, Level 16, State 1, Procedure spc_1, Line 5
Specified owner name 'dbo' either does not exist or you do not have permission to use it.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I guess a DDL-trigger might be the easyest way to do it.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I don't know how DDL trigger is useful in this case but...
You can try granting DDL admin rights....
MohammedU
Microsoft SQL Server MVP -
I think you can make this person an owner of the dbo schema as well.
-
You'll need to grant DDL Admin role privs those users.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The point is the dev-team need to be able to create/alter UDF and SPROCS (CLR is disabled for the moment) and are allowed to manipulate all the data of the database. (so DB_owner groupmembership is the easiest way).
This ddltrigger restricts just that !
create
TRIGGER [ddlDatabaseTriggerNonSA]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
-- Restrict DDL to Func and Sproc for non-sa
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
IF NOT ( @eventType LIKE '%function%'
OR @eventType LIKE '%procedure%' )
BEGIN
IF IS_SRVROLEMEMBER ('sysadmin') = 0
BEGIN
RAISERROR ('You are not entitled to perform this modification [%s]' , 1,1,@eventType) WITH log
ROLLBACK TRAN
END
END
END;
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply