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

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

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';