Solved

policies in sql server

Posted on 2014-12-12
2
160 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

808 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