Create ap_developer and ap_user Roles

USE model GO --------------------------------- -- ap_developer --------------------------------- DECLARE @RoleName sysname set @RoleName = N'ap_developer' IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R') Begin DECLARE @RoleMemberName sysname DECLARE Member_Cursor CURSOR FOR select [name] from dbo.sysusers where uid in ( select member_principal_id from sys.database_role_members where role_principal_id in ( select principal_id FROM sys.database_principals where [name] = @RoleName AND type = 'R' )) OPEN Member_Cursor; FETCH NEXT FROM Member_Cursor into @RoleMemberName WHILE @@FETCH_STATUS = 0 BEGIN exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName FETCH NEXT FROM Member_Cursor into @RoleMemberName END; CLOSE Member_Cursor; DEALLOCATE Member_Cursor; End GO IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ap_developer' AND type = 'R') DROP ROLE [ap_developer] GO IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ap_developer' AND type = 'A') DROP APPLICATION ROLE [ap_developer] GO CREATE ROLE [ap_developer] AUTHORIZATION [dbo] GO EXEC sp_addrolemember N'db_datareader', N'ap_developer' EXEC sp_addrolemember N'db_datawriter', N'ap_developer' GRANT CONTROL ON SCHEMA::[dbo] TO [ap_developer] GRANT VIEW DEFINITION TO [ap_developer] GRANT CREATE PROCEDURE TO [ap_developer] GRANT EXECUTE TO [ap_developer] GO --------------------------------- -- ap_user --------------------------------- DECLARE @RoleName sysname set @RoleName = N'ap_user' IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R') Begin DECLARE @RoleMemberName sysname DECLARE Member_Cursor CURSOR FOR select [name] from dbo.sysusers where uid in ( select member_principal_id from sys.database_role_members where role_principal_id in ( select principal_id FROM sys.database_principals where [name] = @RoleName AND type = 'R' )) OPEN Member_Cursor; FETCH NEXT FROM Member_Cursor into @RoleMemberName WHILE @@FETCH_STATUS = 0 BEGIN exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName FETCH NEXT FROM Member_Cursor into @RoleMemberName END; CLOSE Member_Cursor; DEALLOCATE Member_Cursor; End GO IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ap_user' AND type = 'R') DROP ROLE [ap_user] GO IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ap_user' AND type = 'A') DROP APPLICATION ROLE [ap_user] GO CREATE ROLE [ap_user] AUTHORIZATION [dbo] GO EXEC sp_addrolemember N'db_datareader', N'ap_user' EXEC sp_addrolemember N'db_datawriter', N'ap_user' GRANT VIEW DEFINITION TO [ap_user] GRANT EXECUTE TO [ap_user] GO /********** IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'jmolsen') DROP USER [jmolsen] GO CREATE USER [jmolsen] FOR LOGIN [jmolsen] WITH DEFAULT_SCHEMA=[dbo] GO EXEC sp_addrolemember N'ap_developer', N'jmolsen' GO ************/