Post reply

system tables have logins that have been deleted

  • March 17, 2005 at 6:10 am

    #166334

    Because of SOX (Sarbanes Oxley Act), I created some queries to look at system tables to pull security information.  To my suprise, there are logins in the system tables that have been deleted for more that a year.  I import this information into xls files so they can be retained.  My problem occurs when we are audited by external auditors.  The sit at my desk while I run the queries.  Currently I am keeping with the 30+ screen shots because of it.

    How do I get rid of those in the system table?  Is there a "refresh" or "sync" procedure that needs to be ran after a "click * delete" of a user and login?

    I have more than a dozen queries ready to pull the information needed.  It will reduce my time spent doing quarterly SOX and the annual review by 60%-80%.

    Thanks,

    Joseph

  • March 17, 2005 at 10:26 am

    #547611

    Which tables and in which DBs are you referring? Many times people resotre DB's and forget to remove the invalid users when they go forward. But I need to know what you are looking at specifically.

  • March 17, 2005 at 11:36 am

    #547625

    One of the tables is sysusers in the user databases.  I have not traced the id's to other tables for permissions, roles ect.

     

    Thanks,

    Joseph

  • March 17, 2005 at 11:38 am

    #547626

    What I am looking for is a way to clean up the system tables so the represent the GUI Security--> logins list of SQL Server logins.  If I can't make the queries work, I am stuck doing literally hunderds of screen prints into word documents.

     

    Thanks,

    Joseph

  • March 21, 2005 at 4:27 pm

    #548099

    Hi,

    You have to make sure what you would like: to make logins same as users in the database or make users same as logins.

    What we usually do is to create a cursor that goes through either sysxlogins or sysusers, based on your business requirements and then it adds or drops logins or users in the other table. Otherwise listen what Antares will reply, he usually gives a good advice.

    The example of the cursor may be as follows, but to avoid possible complications I would strongly advise to create a staging table first that will contain the original content of your sysusers, for example mytable (I am making it a regular table)

    I did not test this script, it is just a prototype. We use different similar scripts.

    This one will drop extra users from sysusers if the corresponding login does not exist in sysxlogins. We assume that the user name is same as a login name. If it is not the case, add extra code. Mytable contains an original copy of sysyusers or at least name field of sysusers.

    Yelena

    declare MyCursor cursor for

    select name from mydatabase.dbo.mytable where name not in (select name from master.dbo.syslogins)

    declare @myvar varchar(30)

    open MyCursor

    fetch MyCursor into @myvar

    while (@@fetch_status = 0)

    begin

    print @myvar

    execute sp_dropuser @myvar

    fetch MyCursor into @myvar

    end

    deallocate MyCursor

    Regards,Yelena Varsha

  • March 22, 2005 at 5:38 am

    #548150

    Thanks,

      I have a function working that produces a list of orphaned users.   I used VB then to check box the ones I needed resloved and the ones I needed removed.  I also have several queries that will go through various system tables (sysusers, sysprotect, sysobjects, sysmembers,ect) to pull the information I need.  Then I copy the output directly into imbedded xls files in a work doc.

     

    Thanks for your help,

    Joseph

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply