• Replies 2 replies
  • Subscribers 338 subscribers
  • Views 66 views
  • Users 0 members are here
Related

Cleanup SQL & DB Logins -

Former Member
Former Member $organization

Hello everyone,

I want to clean up our user accounts as we have many people who are not here anymore. I know that I must inactivate accounts when the account has been used. My question...can I delete the SQL login from SQL Security? And can I delete the DB login from impresario and impresario_cci? Or leave the DB logins and delete just the SQL logins?

Gloria

Parents
  • Former Member
    Former Member $organization

    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

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 6/3/2014 1:54:58 PM

    Hello everyone,

    I want to clean up our user accounts as we have many people who are not here anymore. I know that I must inactivate accounts when the account has been used. My question...can I delete the SQL login from SQL Security? And can I delete the DB login from impresario and impresario_cci? Or leave the DB logins and delete just the SQL logins?

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Reply
  • Former Member
    Former Member $organization

    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

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 6/3/2014 1:54:58 PM

    Hello everyone,

    I want to clean up our user accounts as we have many people who are not here anymore. I know that I must inactivate accounts when the account has been used. My question...can I delete the SQL login from SQL Security? And can I delete the DB login from impresario and impresario_cci? Or leave the DB logins and delete just the SQL logins?

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Children
No Data