Geek Answers Handbook

Create Procedure Permission ONLY

I have a requirement in SQL Server 2008 in a development database

  • Only DBAs (database owners) can create, modify tables. The developer must not create or modify tables.
  • Developers can create / modify stored procedures / user-defined functions in the dbo scheme and can execute SP / UDF.
  • Developers must have SELECT, INSERT, DELETE, UPDATE on the tables (tables in the dbo schema

How to do this using the GRANT statement

Found a sample solution from Google, but still a problem

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 is able to block the developer to create the table, but cannot use the block developer to use the SSMS design and modify the table.

Thank.

+5
source share
2

, . , , , .

, ALTER , ALTER . , , , . , ALTER , ALTER .

, , - ALTER , DDL , .

, :

--** 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;
+7

SQL Server 2012:

 USE PermissionDB; 
 ---Select Your database
 CREATE ROLE Employee;
 ---Create role
 CREATE USER Employee1 Without Login;
 ---Create User

 ----Execute the Above query
 EXEC sp_addrolemember @rolename = 'Employee', @membername = 'Employee1';

, ....

-1

More articles:


All Articles