• Replies 2 replies
  • Subscribers 338 subscribers
  • Views 68 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
  • $organization

    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

Reply
  • $organization

    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

Children
No Data