June 14, 2006 at 10:15 am
I need to find out if I can lock down a user/login from having the ability to create, alter, and delete stored procedures.
June 14, 2006 at 11:30 am
By default, normal database users don't have this ability. They only have it if the CREATE PROCEDURE right has been granted to them. In some cases you can use DENY to block such rights if they are receiving them through something other than db_owner rights. But if they are, you're not going to be able to stop them.
K. Brian Kelley
@kbriankelley
June 14, 2006 at 12:02 pm
How is create procedure explicitly granted? I've looked in permissions, but have found nothing. Is the create procedure right built into a database role?
June 14, 2006 at 1:38 pm
June 15, 2006 at 2:20 pm
The db_ddladmin database role allows a user to create a stored procedure.
Greg
Greg
June 15, 2006 at 2:37 pm
True, that one also has implicit permissions to alter the database schema.
K. Brian Kelley
@kbriankelley
June 16, 2006 at 1:35 am
GRANT CREATE PROCEDURE TO [username]
--user name can be a MS SQL Server user, SQL Server role(userrole),NT user or NT group.
--user can create a stored procedure
DENY CREATE PROCEDURE TO [username]
--user cannot create a stored procedure and this action cannot be overridden by role membership
REVOKE CREATE PROCEDURE TO [username]
-- user cannot create a stored procedure but this action can be overridden by role membership
--ex
grant create procedure, create table, create function, create view to [username1], [username2], [username3]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply