I have a requirement in SQL Server 2008 in development database
How to achieve this using GRANT statement
Found a sample solution from Google, but still have issue
CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj 987kj//'
CREATE USER testdev
GRANT ALTER ON SCHEMA::dbo TO testdev
GRANT CREATE PROCEDURE TO testdev
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO testdev
CREATE TABLE mysig (a int NOT NULL)
EXECUTE AS USER = 'testdev'
go
CREATE PROCEDURE slaskis AS PRINT 12
go
CREATE TABLE hoppsan(a int NOT NULL) -- FAILS!
go
INSERT mysig (a) VALUES(123)
go
REVERT
go
DROP PROCEDURE slaskis
DROP TABLE mysig
DROP USER testdev
DROP LOGIN testdev
The syntax above able to block developer to create table but cant block developer to use SSMS design and alter the table.
Thanks.
First of all I would use Roles instead of granting access directly to users. You may be already doing this, but I thought I would mention it.
Okay, the problem here is granting ALTER to the Schema means that the grantee has ALTER access to all object types in the schema. Unfortunately, as far as I know, there is no way to grant permissions to specific object types so it is all or nothing. Conversely, you cannot grant ALTER to all objects and then deny ALTER to specific object types.
The only way I have found to do this is to grant ALTER to the schema and then used a DDL Trigger to control what the role can do.
Here is an updated version of your example demonstrating the principle:
--** Create a Developer Role
CREATE ROLE [Developer] AUTHORIZATION db_securityadmin;
GO
--** Grant view and execute on all SPs to Devloper
--GRANT VIEW DEFINITION ON SCHEMA::dbo TO [Developer];
GRANT CREATE PROCEDURE TO [Developer];
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::dbo TO [Developer]
--** Create user and login for testdev and add to the Developer role
CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj987kj'
CREATE USER testdev
EXEC sp_addrolemember @rolename = 'Developer', @membername = 'testdev';
GO
--** Create DDL trigger to deny drop and alter to the Developer role
CREATE TRIGGER tr_db_DenyDropAlterTable_Dev
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
IF IS_MEMBER('Developer') = 1
BEGIN
PRINT 'You are not authorized to alter or drop a table.';
ROLLBACK TRAN;
END;
END;
GO
--** Testing
CREATE TABLE mysig (a int NOT NULL) ;
EXECUTE AS USER = 'testdev';
GO
CREATE PROCEDURE slaskis AS PRINT 12;
GO
CREATE TABLE hoppsan(a int NOT NULL); -- FAILS!
GO
INSERT mysig (a) VALUES(123);
GO
ALTER TABLE mysig ADD test INT; --** This will fail too
GO
REVERT;
GO
DROP PROCEDURE slaskis ;
DROP TABLE mysig ;
DROP USER testdev;
DROP LOGIN testdev;
DROP ROLE [Developer];
DROP TRIGGER tr_db_DenyDropAlterTable_Dev on DATABASE;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With