Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

policies in sql server

Posted on 2014-12-12
2
Medium Priority
?
173 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 1400 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 600 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

715 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