Monday 23 January 2012

Create User with "Create Procedure Permissions"

USE MASTER
CREATE LOGIN da WITH PASSWORD = '12345'

USE yourdatabasename
GO
CREATE USER da FOR LOGIN da

CREATE ROLE yourrolename

EXEC sp_addrolemember 'db_datareader',yourrolename
EXEC sp_addrolemember 'db_datawriter', yourrolename
EXEC sp_addrolemember @RoleName = 'yourrolename', @MemberName = 'da'

GRANT EXECUTE TO da
GRANT CREATE PROCEDURE TO da

GRANT ALTER ON SCHEMA::dbo TO yourrolename

Check the Isolation Level with DBCC useroptions

DBCC useroptions