March 31, 2009 at 7:01 am
Hi,
I need to create a user with the following permissions.
Create/Alter/Drop Procedure
The user should not be able to Create/Alter/Drop Tables.
Thanx in advance !
March 31, 2009 at 7:07 am
check this
grant user on sp
________________________________________
M.I.
[font="Times New Roman"]
March 31, 2009 at 7:45 am
The user should have Create/Alter/Drop permissions for all the Sp's in the database but should not have Create/Alter/Drop Table permissions in the database.
March 31, 2009 at 10:16 am
I know you can grant CREATE PROCEDURE permission to a user or role, which would allow it to create stored procedures, but you have to grant ALTER permission individually to each procedure after it's created. I think you can grant ALTER permission to the schema to which procedures belong to allow dropping procedures.
Greg
March 31, 2009 at 10:40 am
Most of the objects in this database are under the default schema (dbo).
So giving permissions on the schema won't work.
March 31, 2009 at 12:33 pm
I believe you will need to provide
ALTER ANY SCHEMA
CREATE PROCEDURE
EXECUTE
privileges on the database.
August 27, 2010 at 1:45 pm
[font="Times New Roman"]If we provide
grant alter on schema ::dbo TO user1
then user1 will also be able to drop the tables.
... So then you DENY CONTROL on OBJECT::Table1 to user1 ... User1 is now prevented from dropping Table1
... But User1 can't even select from Table1 anymore. Since DENY is higher precedence than GRANT, you are unable to even GRANT SELECT on object::Table1 to User1[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply