Editor for query blocks from changing views in SQL Server Management Studio

Question

When working with views in SQL Server Management Studio, I prefer script views in ALTER statements, make changes, properly format my code and execute the statement. However, sometimes when I have a script view that I previously worked with, my formatting is lost and I see that many advanced properties are added to my view.

From this, I understand that someone else used the SSMS query editor (constructor) to edit the view, which is annoying as it completely breaks my formatting.

Is there a way to block users from using the constructor to change views in SSMS? Ideally, some database settings, but, alternatively, some hacks that will prevent the designer from opening the view.

A pragmatic approach would simply be to talk to users and force them to stop using the designer, but I’m afraid that old habits become very complicated, and I don’t want to spend another minute reformatting the code that I already formatted many times before ...

Answer 1

No, there is no reliable way to do this.

Adding constructs that the constructor does not support, but which do not change the semantics, may be one of the possibilities

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

However, this does not work. When you open a view, you see a message

SQL text cannot be represented in the grid area and chart pane.

and these panels are empty, but the SQL panel is still displayed and contains reformatted SQL for editing. Also, the foregoing may also adversely affect the implementation plan.

Another approach might be to create a DDL trigger.

The default rows that I see for the designer and query window are "Microsoft SQL Server Management Studio" and "Microsoft SQL Server Management Studio - Query" so you can use it.

 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 does not work until they try to save, and your employees can be very annoyed. The AppName used by the management studio is customizable anyway, so this can also be circumvented.

The only other option I can think of is to look for the Connect site for errors that prevent the designer from opening (I vaguely remember one with the syntax of nested comments), but even if you find it, you risk that they will be fixed in a future package updates.

Answer 2

Microsoft SQL Server Management Studio - Query application name when using a query to change your view: Microsoft SQL Server Management Studio - Query and sql server application name when using a constructor to change your Microsoft SQL Server Management Studio view.

You can populate the DDL trigger for ALTER_VIEW to check APP_Name() and limit the user to use the query instead of the constructor.

 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 a list of sql server views that use the constructor using the following query:

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

I suggest you not restrict the user to APP_Name() , because the user can use the tool application to use the view designer, such as EMS, etc.

You can limit your users using the DDL_EXTENDED_PROPERTY_EVENTS DDL trigger restriction.

 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 

Answer 3

Perhaps you can revoke Alter Schema rights for users, this should prevent them from following ALTER View instructions.

You can transfer ALTER SCHEMA to another user and allow yourself to impersonate, and then use EXECUTE AS to update your view:

 GRANT ALTER ON SCHEMA :: dbo TO user2; REVOKE ALTER ON SCHEMA :: dbo TO user1; GRANT IMPERSONATE ON USER:: user2 TO user1; -- Alter view script EXECUTE AS USER = 'user2'; ALTER VIEW ...... EXECUTE AS USER = 'user1';