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
PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
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]
FOR CREATE_PROCEDURE,ALTER_PROCEDURE, DROP_PROCEDURE,
IF NOT EXISTS(SELECT UserName FROM AuthorizedDDLUser WHERE UserName=CURRENT_USER)
//RAISE CUSTOM ERROR MESSAGE HERE AND ROLLBACK THE TRANSACTION
// ROLLING BACK EFFECTIVELY CANCELS THE DDL STATEMENT
ROLLBACK TRANSACTION --
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.
- 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/
- 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/
- DDL Event List: http://msdn.microsoft.com/en-us/ms189540.aspx
- DDL Triggers: http://msdn.microsoft.com/en-us/library/ms175941.aspx
- 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!
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.