CREATE    PROCEDURE aip_createaiusergroup
(
    @strHost nvarchar(32) = null,
    @strMdb  nvarchar(32) = null
)
AS

--    Set environmewnt
    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON
    
--    Read host from input
    DECLARE @admingrp nvarchar(128)
    DECLARE @pubgrp nvarchar(128)
    DECLARE @authgrp nvarchar(128)
    DECLARE @restgrp nvarchar(128)
    DECLARE @sqlver nvarchar (4)

    IF ( select substring (convert(char(10),SERVERPROPERTY('ProductVersion')),1,1)) = '8'
        set @sqlver = '2000'
    ELSE
        set @sqlver = '2005'     

    SET @admingrp = ltrim(@strHost) + '\AIAdmin'
    SET @pubgrp = ltrim(@strHost) + '\AIPublic'
    SET @authgrp = ltrim(@strHost) + '\AIAuth'
    SET @restgrp = ltrim(@strHost) + '\AIRestricted'


    IF (@sqlver = '2000')
    BEGIN
        PRINT ('Selecting SQL Server version 2000.')
        IF EXISTS (SELECT name FROM master.dbo.syslogins WHERE name = @admingrp)
        begin
            PRINT ('Removing user ''' + @admingrp + ''' from ''' + @strMdb)
            EXEC ('sp_revokelogin ''' + @admingrp + '''' )
            EXEC ('sp_droplogin '''  + @admingrp + '''' )
        end
        IF EXISTS (SELECT name FROM sysusers WHERE name = @admingrp)
        begin
            PRINT ('Removing user ''' + @admingrp + ''' from SYSUSERS')
            EXEC ('sp_revokedbaccess ''' + @admingrp + '''' )
    --        EXEC ('sp_droplogin '''  + @admingrp + '''' )
        end


        PRINT ('Creating user ''' + @admingrp + ''' in ''' + @strMdb)
        exec ('sp_grantlogin '''+ @admingrp +'''' )
        exec ('sp_defaultdb ''' + @admingrp + ''', ''' + @strMdb + '''')
        exec ('sp_grantdbaccess ''' + @admingrp + '''')
        exec ('sp_addrolemember ''db_datareader'', ''' + @admingrp + '''')
        exec ('sp_addrolemember ''db_datawriter'', ''' + @admingrp + '''')


        IF EXISTS (SELECT name FROM master.dbo.syslogins WHERE name = @pubgrp  )
        begin
            PRINT ('Removing user ''' + @pubgrp + ''' from ''' + @strMdb)
            EXEC ('sp_revokelogin ''' + @pubgrp + '''' )
            EXEC ('sp_droplogin '''  + @pubgrp + '''' )
        end

        IF EXISTS (SELECT name FROM sysusers WHERE name = @pubgrp)
        begin
            PRINT ('Removing user ''' + @pubgrp + ''' from SYSUSERS')
            EXEC ('sp_revokedbaccess ''' + @pubgrp + '''' )
        end


        PRINT ('Creating user ''' + @pubgrp + ''' in ''' + @strMdb)
        exec ('sp_grantlogin '''+ @pubgrp +'''' )
        exec ('sp_defaultdb ''' + @pubgrp + ''', ''' + @strMdb + '''')
        exec ('sp_grantdbaccess ''' + @pubgrp + '''' )
        exec ('sp_addrolemember ''db_datareader'', ''' + @pubgrp + '''')
        exec ('sp_addrolemember ''db_datawriter'', ''' + @pubgrp + '''')


        IF EXISTS (SELECT name FROM master.dbo.syslogins WHERE name = @authgrp )
        begin
            PRINT ('Removing user ''' + @authgrp + ''' from ''' + @strMdb)
            EXEC ('sp_revokelogin ''' + @authgrp + '''' )
            EXEC ('sp_droplogin '''  + @authgrp + '''' )
        end

        IF EXISTS (SELECT name FROM sysusers WHERE name = @authgrp )
        begin
            PRINT ('Removing user ''' + @authgrp + ''' from SYSUSERS')
            EXEC ('sp_revokedbaccess ''' + @authgrp + '''' )
        end


        PRINT ('Creating user ''' + @authgrp + ''' in ''' + @strMdb)
        exec ('sp_grantlogin '''+ @authgrp +'''' )
        exec ('sp_defaultdb ''' + @authgrp + ''', ''' + @strMdb + '''')
        exec ('sp_grantdbaccess ''' + @authgrp + '''' )
        exec ('sp_addrolemember ''db_datareader'', ''' + @authgrp + '''')
        exec ('sp_addrolemember ''db_datawriter'', ''' + @authgrp + '''')

        IF EXISTS (SELECT name FROM master.dbo.syslogins WHERE name = @restgrp )
        begin
            PRINT ('Removing user ''' + @restgrp + ''' from ''' + @strMdb)
            EXEC ('sp_revokelogin ''' + @restgrp + '''' )
            EXEC ('sp_droplogin '''  + @restgrp + '''' )
        end

        IF EXISTS (SELECT name FROM sysusers WHERE name = @restgrp )
        begin
            PRINT ('Removing user ''' + @restgrp + ''' from SYSUSERS')
            EXEC ('sp_revokedbaccess ''' + @restgrp + '''' )
        end


        PRINT ('Creating user ''' + @restgrp + ''' in ''' + @strMdb)
        exec ('sp_grantlogin '''+ @restgrp +'''' )
        exec ('sp_defaultdb ''' + @restgrp + ''', ''' + @strMdb + '''')
        exec ('sp_grantdbaccess ''' + @restgrp + '''' )
        exec ('sp_addrolemember ''db_datareader'', ''' + @restgrp + '''')
        exec ('sp_addrolemember ''db_datawriter'', ''' + @restgrp + '''')
    END
    ELSE
    BEGIN
        PRINT ('Selecting SQL Server version 2005.')
        IF EXISTS (SELECT name FROM master.dbo.syslogins WHERE name = @admingrp)
        begin
            PRINT ('Removing user ''' + @admingrp + ''' from ''' + @strMdb)
            EXEC ('DROP LOGIN [' + @admingrp + ']'  )
--            EXEC ('sp_revokelogin ''' + @admingrp + '''' )
--            EXEC ('sp_droplogin '''  + @admingrp + '''' )
        end
        IF EXISTS (SELECT name FROM sysusers WHERE name = @admingrp)
        begin
            PRINT ('Removing user ''' + @admingrp + ''' from SYSUSERS')
            EXEC ('sp_revokedbaccess ''' + @admingrp + '''' )
    --        EXEC ('sp_droplogin '''  + @admingrp + '''' )
        end


        PRINT ('Creating user ''' + @admingrp + ''' in ''' + @strMdb)
        exec ('sp_grantlogin '''+ @admingrp +'''' )
        exec ('sp_defaultdb ''' + @admingrp + ''', ''' + @strMdb + '''')
        exec ('sp_grantdbaccess ''' + @admingrp + '''')
        exec ('sp_addrolemember ''db_datareader'', ''' + @admingrp + '''')
        exec ('sp_addrolemember ''db_datawriter'', ''' + @admingrp + '''')


        IF EXISTS (SELECT name FROM master.dbo.syslogins WHERE name = @pubgrp  )
        begin
            PRINT ('Removing user ''' + @pubgrp + ''' from ''' + @strMdb)
            EXEC ('DROP LOGIN [' + @pubgrp + ']'  )
--            EXEC ('sp_revokelogin ''' + @pubgrp + '''' )
--            EXEC ('sp_droplogin '''  + @pubgrp + '''' )
        end

        IF EXISTS (SELECT name FROM sysusers WHERE name = @pubgrp)
        begin
            PRINT ('Removing user ''' + @pubgrp + ''' from SYSUSERS')
            EXEC ('sp_revokedbaccess ''' + @pubgrp + '''' )
        end


        PRINT ('Creating user ''' + @pubgrp + ''' in ''' + @strMdb)
        exec ('sp_grantlogin '''+ @pubgrp +'''' )
        exec ('sp_defaultdb ''' + @pubgrp + ''', ''' + @strMdb + '''')
        exec ('sp_grantdbaccess ''' + @pubgrp + '''' )
        exec ('sp_addrolemember ''db_datareader'', ''' + @pubgrp + '''')
        exec ('sp_addrolemember ''db_datawriter'', ''' + @pubgrp + '''')


        IF EXISTS (SELECT name FROM master.dbo.syslogins WHERE name = @authgrp )
        begin
            PRINT ('Removing user ''' + @authgrp + ''' from ''' + @strMdb)
            EXEC ('DROP LOGIN [' + @authgrp + ']'  )
--            EXEC ('sp_revokelogin ''' + @authgrp + '''' )
--            EXEC ('sp_droplogin '''  + @authgrp + '''' )
        end

        IF EXISTS (SELECT name FROM sysusers WHERE name = @authgrp)
        begin
            PRINT ('Removing user ''' + @authgrp + ''' from SYSUSERS')
            EXEC ('sp_revokedbaccess ''' + @authgrp + '''' )
        end


        PRINT ('Creating user ''' + @authgrp + ''' in ''' + @strMdb)
        exec ('sp_grantlogin '''+ @authgrp +'''' )
        exec ('sp_defaultdb ''' + @authgrp + ''', ''' + @strMdb + '''')
        exec ('sp_grantdbaccess ''' + @authgrp + '''' )
        exec ('sp_addrolemember ''db_datareader'', ''' + @authgrp + '''')
        exec ('sp_addrolemember ''db_datawriter'', ''' + @authgrp + '''')


        IF EXISTS (SELECT name FROM master.dbo.syslogins WHERE name = @restgrp )
        begin
            PRINT ('Removing user ''' + @restgrp + ''' from ''' + @strMdb)
            EXEC ('DROP LOGIN [' + @restgrp + ']'  )
--            EXEC ('sp_revokelogin ''' + @restgrp + '''' )
--            EXEC ('sp_droplogin '''  + @restgrp + '''' )
        end

        IF EXISTS (SELECT name FROM sysusers WHERE name = @restgrp)
        begin
            PRINT ('Removing user ''' + @restgrp + ''' from SYSUSERS')
            EXEC ('sp_revokedbaccess ''' + @restgrp + '''' )
        end


        PRINT ('Creating user ''' + @restgrp + ''' in ''' + @strMdb)
        exec ('sp_grantlogin '''+ @restgrp +'''' )
        exec ('sp_defaultdb ''' + @restgrp + ''', ''' + @strMdb + '''')
        exec ('sp_grantdbaccess ''' + @restgrp + '''' )
        exec ('sp_addrolemember ''db_datareader'', ''' + @restgrp + '''')
        exec ('sp_addrolemember ''db_datawriter'', ''' + @restgrp + '''')
    END
    

GO