You'll have to grant them appropriate permissions at the database level to indicate what they should be able to do. Then grant ALTER permission (or make them the owner) on the schemas in which they can do these things.
GRANT CREATE TABLE, CREATE PROCEDURE TO [AD\dbusersgroup_limitedrights]
GRANT ALTER ON SCHEMA::[DIS] TO [AD\dbusersgroup_limitedrights]
Triggers run, by default, under the security context of the principal who caused the trigger to fire.
In order to change this behavior, you'll need to create the trigger using the WITH EXECUTE AS OWNER clause.
Below is an example which shows how that works. WITH EXECUTE AS OWNER allows the trigger to run in the security context of the database owner, instead of the principal who is updating the table.
First, we create a test table:
USE tempdb;
IF OBJECT_ID('dbo.t') IS NOT NULL
BEGIN
DROP TRIGGER t_trig;
DROP TABLE dbo.t;
END
GO
CREATE TABLE dbo.t
(
ID INT NOT NULL
, ID2 INT NULL
);
GO
Here's the trigger code, with EXECUTE AS OWNER:
CREATE TRIGGER t_trig ON dbo.t
WITH EXECUTE AS OWNER
AFTER INSERT
AS
BEGIN
UPDATE dbo.t
SET ID2 = ID
WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.ID = dbo.t.ID);
END
GO
Now, we'll create a test login with low privileges that can be used to test the hypothesis that EXECUTE AS OWNER allows an under-privileged principal access to functionality they otherwise would not have:
CREATE LOGIN tLogin WITH PASSWORD = 'QWERFsdf23454%';
CREATE USER tLogin FROM LOGIN tLogin WITH DEFAULT_SCHEMA = dbo;
We'll give them the ability to insert rows into dbo.T, but prevent them from running UPDATE statements:
GRANT INSERT ON dbo.t TO tLogin;
DENY UPDATE ON dbo.t TO tLogin;
Here we do the test:
EXECUTE AS USER = 'tLogin';
/*
Output here shows we're running under the tLogin
security context
*/
SELECT SUSER_SNAME();
/*
This will fail, with insufficient privileges
since we've DENY'd the UPDATE privilege to tLogin.
*/
UPDATE dbo.t SET ID2 = ID;
/*
this will run the UPDATE since the trigger
has EXECUTE AS OWNER
*/
INSERT INTO dbo.t(ID) VALUES (1);
/*
This takes us out of the tLogin security context
*/
REVERT
Here we can see the row in dbo.T has the changes made by the trigger:
SELECT *
FROM dbo.t;
And here, we cleanup the low-privilege user:
DROP USER tLogin;
DROP LOGIN tLogin;
The output from running the above is:
Best Answer
Short Answer: you can't.
If the database is being run on the customer's infrastructure, then the customer must have control over their own infrastructure. Without it, they cannot operate.
If you really don't want them to have that control, then you cannot give them the database. This is the "Software-as-a-Service" Operating Model wherein you licence the application but don't get to play with the "back-end" of things.
I would suggest that this is not a Technical problem. It's a Licencing one.
I assume that the licence agreement for your software includes a provision that prevents any Customer from "Reverse Engineering" any part of your product. That clause should be extended to include your database.
Any "mucking about" with the database should invalidate [at least] your support agreement with that Customer. If they want to take that Risk, well, that's up to them.