Block Query Editor from altering views in SQL Server Management Studio

Question

When working with views in SQL Server Management Studio, I prefer to script the views into ALTER-statements, make my changes, format my code properly and then execute the statement. However, sometimes when I script a view that I have previously been working with, my formatting is lost and I see a whole lot of extended properties added to my view.

From this I gather, that somebody else has been using the SSMS Query Editor (designer) to edit the view, which is annoying since it completely breaks my formatting.

Is there a way to block users from using the designer to alter views in SSMS? Ideally some database setting, but alternatively, some hack that would prevent the designer from opening the view.

The pragmatic approach would simply be to talk to the users and make them stop using the designer, but I'm afraid old habits die hard, and I don't want to spend another minute reformatting code that I've already formatted many times before...

Answer 1

No there isn't a robust way of doing this.

Adding constructs that the designer does not support but that do not change the semantics might be one possibility

WHERE (1 = (SELECT ROW_NUMBER() OVER (ORDER BY @@SPID)))

However it doesn't really work. On opening the view you see the message

SQL text cannot be represented in the grid pane and diagram pane.

and these panes are blank but the SQL pane still appears and contains the reformatted SQL for editing. Also the above has the risk of changing the execution plan in a negative way too.

Another approach might be to create a DDL trigger.

The default program_name strings I see for the designer vs a query window are "Microsoft SQL Server Management Studio" and "Microsoft SQL Server Management Studio - Query" so you could use.

CREATE TRIGGER NoAlterViewFromSSMS 
ON DATABASE 
FOR ALTER_VIEW
AS 
   IF APP_NAME() = 'Microsoft SQL Server Management Studio'
   BEGIN
       RAISERROR ('Please don''t use the designer to ALTER views',16, 1)
       ROLLBACK
   END
GO

But this will not fire until they attempt to save and your co workers might be highly annoyed. The AppName used by management studio is configurable anyway so this could also be circumvented.

The only other option I can think of would be to search the Connect site for bugs that prevent the designer from opening (I vaguely remember one with nested comment syntax) but even if you find one you are at risk that they will be fixed in a future service pack.

Answer 2

The application name of sql server when you use query for alter your view, is Microsoft SQL Server Management Studio - Query and application name of sql server when you use designer for alter your view is Microsoft SQL Server Management Studio.

You can crate DDL Trigger for ALTER_VIEW in order to check APP_Name() and limit your user to use query instead of designer.

CREATE TRIGGER LimitUseDesignerForView ON DATABASE 
FOR ALTER_VIEW
AS 
   IF APP_NAME() = 'Microsoft SQL Server Management Studio'
   BEGIN
       RAISERROR ('Use query in order to alter your view',16, 1)
       ROLLBACK
   END
GO

You can also get list of sql server views that use designer by following query:

SELECT DISTINCT OBJECT_NAME(ep.major_id) 
FROM sys.extended_properties ep
WHERE ep.name LIKE 'MS_DiagramPane%'

I suggest that you don't limit your user by APP_NAME() because user maybe use tools application in order to use view designer such as EMS and etc.

You can limit your users by using limitation on DDL_EXTENDED_PROPERTY_EVENTS DDL trigger.

CREATE TRIGGER LimitUseDesignerForView ON DATABASE 
FOR DDL_EXTENDED_PROPERTY_EVENTS
AS 
    IF (EVENTDATA().value('(/EVENT_INSTANCE/Parameters/Param)[1]','nvarchar(max)') LIKE 'MS_DiagramPane%') BEGIN
        RAISERROR ('Use query in order to alter your view',16, 1)
        ROLLBACK 
    End
GO