Is there a way to DENY CRUD operations on connections from linked server?

Question

I have a SQL Server instance, RPT1 (2008 R2), that was set up for "power users" to run their own queries. This server is linked to our production reporting server, PRD1 (SQL Server 2008 instance), with views (on RPT1) that query the linked server (PRD1) directly.

The linked server has been created with an account that requires the ability to CRUD on the server (for report auditing, and such).

As the users gain sophistication, they also request greater permissions (e.g. to create their own views). I'd like to ensure that they are not going to be doing any CRUD operations on the linked server, but the account with which it was linked has those permissions.

The first resolution path that I see is to create a different user, with lower permissions, and re-link the server.

My second path is to see if there's a way to explicitly DENY CRUD on the PRD1 server for connections coming from a linked server.

Is it possible to explicitly deny CRUD only from the link? Or should I simply set about re-linking the server with a different account? Is there another obvious solution that I'm overlooking?

Answer 1

Have you had a look at setting up a Server wide DDL trigger? I have used this successfully on several production servers to block unauthorized changes made by developers. I joined a company where ALL developers had full SA access.

When we tried to change their rights we had a full blown revolution as the developers all of a sudden 'could not do their work'. So I used a server wide DDL trigger to limit what they could do. It was very simple.

I created a table called AuthorizedDDLUser which had a very simple structure something like this:

 CREATE TABLE [dbo].[AuthorizedDDLUser](
    [Username] [nvarchar](256) NOT NULL,
    CONSTRAINT [PK_AuthorizedDDLUser] PRIMARY KEY CLUSTERED 
    (
      [Username] ASC
     )
    WITH (
           PAD_INDEX  = OFF
           , STATISTICS_NORECOMPUTE  = OFF
           , IGNORE_DUP_KEY = OFF
           , ALLOW_ROW_LOCKS  = ON
           , ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

  GO

I then inserted the Login names of the developers which I would allow to make changes(i.e. the DBA team and Senior SQL developers).

I then created a server wide DDL trigger similar to this:

CREATE TRIGGER [block_ddl]
ON SERVER
FOR CREATE_PROCEDURE,ALTER_PROCEDURE, DROP_PROCEDURE, 
    CREATE_TABLE,ALTER_TABLE, DROP_TABLE, 
    CREATE_FUNCTION,ALTER_FUNCTION, DROP_FUNCTION, 
    CREATE_INDEX,ALTER_INDEX, DROP_INDEX, 
    CREATE_VIEW,ALTER_VIEW, DROP_VIEW,
    CREATE_TRIGGER,ALTER_TRIGGER, DROP_TRIGGER
AS
    IF NOT EXISTS(SELECT UserName FROM AuthorizedDDLUser WHERE UserName=CURRENT_USER)
    BEGIN
            //RAISE CUSTOM ERROR MESSAGE HERE AND ROLLBACK THE TRANSACTION 
            // ROLLING BACK EFFECTIVELY CANCELS THE DDL STATEMENT
            ROLLBACK TRANSACTION --
    END

My implementation was a little bit more involved as it also did auditing. However I am trying to keep it simple here.

So in a nutshell you can selectively limit what the linked user can do on the system. To really get started with this I would suggest your read the following articles.

1. Using a DDL Trigger to Block Schema Changes in SQL Server: http://www.mssqltips.com/sqlservertip/2646/using-a-ddl-trigger-to-block-schema-changes-in-sql-server/
2. Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005: http://www.mssqltips.com/sqlservertip/1006/auditing-ddl-create-alter-drop-commands-in-sql-server-2005/
3. DDL Event List: http://msdn.microsoft.com/en-us/ms189540.aspx
4. DDL Triggers: http://msdn.microsoft.com/en-us/library/ms175941.aspx
5. Using the EventData() Function with DDL triggers in SQL Server 2005: http://www.mssqltips.com/sqlservertip/1571/using-the-eventdata-function-with-ddl-triggers-in-sql-server-2005/

So in my case the developers could still do pretty much anything except mess up my system. Eventually we got them weaned off SA access and my life became normal again thanks to DDL triggers.

Hope that helps!

UPDATE:

Thanks to Martin Smith for pointing out that these are after triggers. Something I did not realise until now (you learn everyday). The MSDN article is very confusing in that it indicates it is both a before and after trigger. So yes this could open some potential for performance issues if like Martin said you rollback a index build on a rather large table. However if you are just going to block view and tables from being created then it should be ok.

So use wisely.

They really need to fix this as it limits the usability of this feature.