Cleanup SQL & DB Logins -

Thanks so much Kevin! Excellent.

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Kevin Holmes
Sent: Tuesday, June 03, 2014 4:21 PM
To: Gloria Ormsby
Subject: Re: [Tessitura Technical Forum] Cleanup SQL & DB Logins -

Hi Gloria,

We delete the SQL login and the database user/schema info.  We get 10-15 interns every semester and the inactive logins can pile up, so I wrote the script below to help with that cleanup, which saves having to manually delete the accounts from each database and then delete the login.

Kevin.

/*
Removes logins and schemas from the databases where the associated
Tessitura account is inactive.
*/
USE impresario

DECLARE Names CURSOR FOR (
    SELECT MU.userid
    FROM T_METUSER MU
    INNER JOIN sys.database_principals DP ON DP.name = MU.db_userid
    WHERE MU.inactive = 'Y'
)

DECLARE @UserID VARCHAR(30)

OPEN Names

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM Names INTO @UserID
    IF @@FETCH_STATUS <> 0 BREAK

    PRINT 'Dropping login and schema for: ' + @UserID
   
    --Drop user from IMPRESARIO
    USE impresario
    EXEC('DROP SCHEMA ' + @UserID)
    EXEC('DROP USER ' + @UserID)
   
    --Drop user from IMPRESARIO_CCI
    USE impresario_cci
    EXEC('DROP SCHEMA ' + @UserID)
    EXEC('DROP USER ' + @UserID)
   
    --Drop SQL Login
    USE impresario
    EXEC('DROP LOGIN ' + @UserID)
END

CLOSE Names
DEALLOCATE Names