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