September 21, 2010 at 4:26 pm
I believe I can run this statement so that TestUser can create procedures in the Database.
GRANT CREATE PROCEDURE TO TestUser
I also believe there is NO "GRANT ALTER PROCEDURE TO TestUser" statement. So I have to "GRANT ALTER to Schema". Which allows ALTER to all objects, which I do not want.
How can I, or maybe I can not, enable the developer to ALTER the PROCEDURE and DROP the PROCEDURE after I GRANT them the CREATE PROCEDURE permission? Without allowing them to perform other DDL commands?
...thanks
September 21, 2010 at 8:26 pm
Have you tested altering a proc after granting create? IIRC, create also grants the ability to alter.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 22, 2010 at 8:32 am
Thanks for the reply.
I GRANT CREATE PROCEDURE to the User, logged into SSMS as that user and tried to create an SP.
It FAILED.
"The specified schema name "dbo" either does not exist or you do not have permission to use it."
DBO does exist.
I did notice that there is a LOCK icon over all the existing SPs?!
I then tried a sp_helptext of an existing SP and it FAILED.
"There is no text for object 'dbo.MyProcedure'.
I'm missing some other type of permission to even VIEW existing SPs.
Based on BOL, the User must also have "ALTER PERMISSION ON THE SCHEMA"?!
Is this correct? Doesn't this mean that can ALTER ANY Object in the Database? I do not want this.
...thanks
September 22, 2010 at 9:10 am
I did a "GRANT VIEW DEFINITION to TestUser", so now the LOCK ICONs are gone and I can do a sp_helptext.
However, TestUser still can NOT Create an SP. I'm missing some other permission other than CREATE PROCEDURE permission.
September 22, 2010 at 9:19 am
Looking at BOL, it says that besides CREATE PROCEDURE, the User must also be GRANTed ALTER Schema like this?
GRANT ALTER ON SCHEMA::dbo TO TestUser
Doesn't that give the User too much authority to ALTER anything in the Schema?
I just need to enable the User to Create a Stored Procedure?
September 22, 2010 at 11:08 am
Are you trying to create a proc as follows:
Create procedure dbo.someproc
or
Create procedure testuser.someproc
?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 22, 2010 at 11:11 am
Yes, as DBO.
I ended up doing a GRANT ALTER as stated by BOL. That worked. Although I think it allows too many permissions.
September 25, 2010 at 7:06 am
Yes that will give them more permissions than you want on dbo.
If your goal is to allow them to do that under the dbo schema, then that is what you will need to do. If your goal is to simply allow them to create and modify their own procs, then they would need to create a proc with their schema name in lieu of dbo.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 27, 2010 at 8:02 am
CirquedeSQLeil (9/25/2010)
Yes that will give them more permissions than you want on dbo.If your goal is to allow them to do that under the dbo schema, then that is what you will need to do. If your goal is to simply allow them to create and modify their own procs, then they would need to create a proc with their schema name in lieu of dbo.
Yep, the SPs must be under DBO. So ALTER is the solution.
...thanks
September 27, 2010 at 8:54 am
You're welcome
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply