June 6, 2002 at 7:44 am
Hello All,
We're going to be retrofitting databases in the near future with security (you don't want to know how users were connecting). As you can imagine trying to apply security to a system that is already in production is somewhat of a nuisance.
A request has been made of me to monitor the security/permission after the update. The initial suggestion was that I run a daily query to check permissions have not been changed. Obviously if security is set up correctly this contiuous monitoring should not be needed; however the request has been made so a solution has to be provided.
My approach, with my limited knowledge, would be to have a profiler trace running all the time to write changes to permissions to a table. This could then be checked in the mornings to see if any errant developer had manage to fiddle with his permissions.
Anyone any suggestions
DeltaKilo
June 6, 2002 at 8:13 am
You might be able to try something like this to catch changes to user permissions:
select * from databasename.dbo.sysusers
where updatedate > dateadd(dd,-1,getdate())
If you capture a copy of the table after you make your security changes you could find out what changed... maybe with something like this:
if not exists(select * from sysobjects where type = 'u' and name = 'sysusers')
select * into databasename.dbo.sysusers_backup from databasename.dbo.sysusers
Well I think you just gave me an idea for a new security change monitoring system that can be run across all databases on a server.
Thanks,
Dan
June 6, 2002 at 8:21 am
If you want to try the method with profiler you can start a trace on the server and set it so you can see who made the changes as well. The key is which version of SQL are you running.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 6, 2002 at 8:31 am
Oh and you can do the same for the master..syslogins table
June 7, 2002 at 3:22 pm
I decided to write a stored procedure that reports on account changes based on the syslogins and sysusers tables only (there's no datetime column on the syspermissions table). This procedure will not catch table or object level permission changes, nor would you know what user made the change like you would see in profiler. However it is much less overhead than running profiler.
I submitted the procedure to the scripts library this morning so it should show up in the next day or two.
Check it out if you're interested.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply