March 17, 2005 at 6:11 am
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 18, 2005 at 5:01 pm
A database user cannot be deleted if it owns objects in the database. Take one user and execute:
exec sp_dropuser 'username'
or
exec database_name..sp_dropuser 'username'
The error message from this should confirm this. If the user owns 'objects' then you have a bit of a mess to cleanup. If the user does not, it will be dropped from the current database
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 21, 2005 at 6:17 am
Thanks for the info. I have it cleaned up now. I put together a script to take care of those issues when we do refreshes.
Joseph
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply