Trigger to restrict user froom Deleting Procedures

Question

Trigger to restrict user froom Deleting Procedures

Rechana Rajan

SSCertifiable

Points: 7717
More actions
November 21, 2016 at 1:23 am

#332026

How can i restrict a user who is having create\drop object permission on a database from deleting stored procedures.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can create a DDL trigger to roll back the DROP PROCEDURE, but if the user has CREATE & DROP permissions on the database, they'll be able to drop the trigger.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

If you grant the user privileges at the schema level instead of the database level, then they could create and alter procedures at will, but not be allowed to drop the database level trigger that prevents them from dropping the procedures.

Here's a setup:

CREATE ROLE [programmer];

GRANT SELECT,INSERT,UPDATE,DELETE ON SCHEMA::[dbo] TO [programmer];

GRANT ALTER ON SCHEMA::[dbo] TO [programmer];

GRANT EXECUTE ON SCHEMA::[dbo] TO [programmer];

GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [programmer];

GRANT REFERENCES ON SCHEMA::[dbo] TO [programmer];

GRANT CREATE FUNCTION, CREATE PROCEDURE, CREATE VIEW, CREATE TABLE TO [programmer];

then a database level trigger:

CREATE TRIGGER nodropproc ON DATABASE

FOR DROP_PROCEDURE

AS

IF ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0

BEGIN

PRINT 'You must disable Trigger "nodropproc" to drop procedures';

ROLLBACK;

END;

So a user would have to be in sysadmin server role or else they would be prevented from dropping a procedure. A user in the database role [programmer] would not be able to drop the database level trigger.

Rechana Rajan SSCertifiable Points: 7717 More actions · Answer 3

GilaMonster (11/21/2016)
You can create a DDL trigger to roll back the DROP PROCEDURE, but if the user has CREATE & DROP permissions on the database, they'll be able to drop the trigger.

Thanks Gail

Rechana Rajan SSCertifiable Points: 7717 More actions · Answer 4

Chris Harshman (11/22/2016)
If you grant the user privileges at the schema level instead of the database level, then they could create and alter procedures at will, but not be allowed to drop the database level trigger that prevents them from dropping the procedures.
Here's a setup:
CREATE ROLE [programmer];
GRANT SELECT,INSERT,UPDATE,DELETE ON SCHEMA::[dbo] TO [programmer];
GRANT ALTER ON SCHEMA::[dbo] TO [programmer];
GRANT EXECUTE ON SCHEMA::[dbo] TO [programmer];
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [programmer];
GRANT REFERENCES ON SCHEMA::[dbo] TO [programmer];
GRANT CREATE FUNCTION, CREATE PROCEDURE, CREATE VIEW, CREATE TABLE TO [programmer];
then a database level trigger:
CREATE TRIGGER nodropproc ON DATABASE
FOR DROP_PROCEDURE
AS
IF ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0
BEGIN
PRINT 'You must disable Trigger "nodropproc" to drop procedures';
ROLLBACK;
END;
So a user would have to be in sysadmin server role or else they would be prevented from dropping a procedure. A user in the database role [programmer] would not be able to drop the database level trigger.

Thanks a lot Chris.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

If you want to prevent some from the whole set of stored procedures from dropping:

CREATE TRIGGER [TR_PreventDropProcedure] ON DATABASE

FOR DROP_PROCEDURE

AS

BEGIN

SET NOCOUNT ON;

DECLARE @data XML

SET @data = EVENTDATA();

DECLARE @objname nvarchar(200);

DECLARE @objtype nvarchar(50);

--stored procedures

DECLARE @dwh_sp_list TABLE(obj_name nvarchar(100) PRIMARY KEY)

INSERT INTO @dwh_sp_list(obj_name)

VALUES('sp1'),('sp2'),('sp3')

SELECT @objname=@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)'),

@objtype=@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(50)')

FROM [master].sys.sysprocesses

WHERE spid = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

IF @objtype = 'PROCEDURE'

BEGIN

IF EXISTS (SELECT * FROM @dwh_sp_list WHERE obj_name = @objname)

BEGIN

RAISERROR('You are not allowed to drop stored procedures.',-1,-1);

ROLLBACK;

END

GO

Igor Micev,My blog: www.igormicev.com