Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
My first inclination would be to add a second linked server with a different name, and use synonyms to expose only the objects the users can access, and deny them access to the original linked server. –  Aaron Bertrand May 24 '12 at 17:18
    
I think that Aaron's suggestion is practical. If those users have permissions on the PROD server and you want to stretch your skills a bit, I would look at getting integrated security working (it can be as easy as manually creating an SPN, or allowing the domain login to create SPNs). That way, the users can't do anything through the link that they couldn't do if they logged directly onto the server. If they don't have login rights on the prod box, this isn't doable. OT:I'd pay attention to any new views that they set up, sometimes performance through a linked server is surprisingly bad. –  darin strait May 25 '12 at 0:48
add comment

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
2  
Worth pointing out that these are after triggers though so will only rollback after the fact. A CREATE_INDEX on a large table followed by a rollback could still do quite a lot of damage for example. –  Martin Smith May 30 '12 at 8:08
    
If the index was already created indeed. So as far I remember they are before triggers.According to MSDN article they are definitely before triggers. –  Namphibian May 30 '12 at 8:16
    
That is not correct. They are after triggers. A ROLLBACK in a DDL trigger happens after the event has happened. DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers. –  Martin Smith May 30 '12 at 8:16
    
According to MSDN: You can use DDL triggers to audit database or server operations that create, modify, or delete database objects or ensure that DDL statements enforce your business rules before they are run. Link found here: msdn.microsoft.com/en-us/library/ms175941.aspx –  Namphibian May 30 '12 at 8:18
    
Same page my quote is from! –  Martin Smith May 30 '12 at 8:19
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.