Solved

policies in sql server

Posted on 2014-12-12
2
170 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 27

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 43

Assisted Solution

by:Eugene Z
Eugene Z 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

623 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