Solved

policies in sql server

Posted on 2014-12-12
2
161 Views
Last Modified: 2014-12-24
Hi experts:

this trigger:
CREATE TRIGGER [ddl_trigger_create_proc]
ON DATABASE
AFTER CREATE_PROCEDURE
AS
DECLARE @name nvarchar(128)
SET @name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
IF @name LIKE 'sp[_]%'
BEGIN
    RAISERROR ('You cannot create a stored procedure with a name starting with "sp_".  Re-create with a different name!', 16, 1)
    ROLLBACK TRANSACTION
END --IF
GO

This trigger can do with policies or directives of sql server? if so please outline the steps to do so.
0
Comment
Question by:enrique_aeo
2 Comments
 
LVL 26

Accepted Solution

by:
Zberteoc earned 350 total points
ID: 40497934
This question is not very clear. What do you mean "trigger can do?".

On the other hand I think this is a bit too extreme. You don't need a trigger to prevent that naming convention. Even if you use it it i snot such a big deal. The only problem with "sp_" is that if you don't specify the database name when invoke the procedure the SQL engine will look for it in the master database first and if is not there it will look in your current database.

In some cases this is intentional and used by the best experts in SQL world, i.e., Brent Ozar with his sp_blitz, Adam Machanic with sp_whoisactive and Ola Hallegren with his maintenance solution. They created specialized procedures and solutions that help a lot with SQL administration and they all named their procedures with sp_ so that you can create them in the master database if you want. You don't have to, though, I prefered to create a dedicated DBA database where I created all these.

The advantage of creating an sp_ named procedure in master databse is that you can invoke it simply by name form any database you are in. If you create them in a dedicated database you either have to be in that database or to specify the database name when you invoke them from another one.
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 150 total points
ID: 40498442
please check this article with examples
DDL Triggers in SQL Server - audit database objects
Why do we need DDL triggers?
http://www.sqlbook.com/SQL-Server/DDL-Triggers-in-SQL-Server-34.aspx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question