system tables have logins that have been deleted

Question

system tables have logins that have been deleted

devereauxj

Hall of Fame

Points: 3263
More actions
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

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

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.

devereauxj Hall of Fame Points: 3263 More actions · Answer 2

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

devereauxj Hall of Fame Points: 3263 More actions · Answer 3

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

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

devereauxj Hall of Fame Points: 3263 More actions · Answer 5

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