Have a scenario where we have to audit all our databases and servers for changes in security accross the servers.
We have a central montioring server where we pull SQL metadata at regular intervals. In this instance we are looking to have before and after snapshots of the SQL system tables.
For the logins this is fine as there is a last updated field in the syslogins table. We can tell when a new user has been added , remmoved or the login has changed ie the login as been added to sysadmin fixed server role... etc Perfect !!!
What Im trying to work out now is how I can do this for object level permsisions. Have looked at sysprotects but no joy. If a user or a role has been granted select or update on a table... How can I tell based on before and after snap shots of the system tables what permissons have changed and whom have they changed for...
You can confirm all of this by creating a new table in say the pubs database, and bounce the permissions around. UID and ID should be relatively obvious. After that, how to monitor changes is up to you.
sysusers.name as [Role or User Name],
WHEN 1 THEN 'Role' ELSE 'User' END AS [Type],
sysobjects.name AS [Object Name],
sysobjects.type AS [Object Type],
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 224 THEN 'EXECUTE PROC'
WHEN 198 THEN 'CREATE TABLE'
WHEN 207 THEN 'CREATE VIEW'
WHEN 222 THEN 'CREATE PROC'
WHEN 233 THEN 'CREATE DEFAULT'
WHEN 236 THEN 'CREATE RULE'
WHEN 26 THEN 'DRI'
ELSE 'No Permissions set'
END AS [Permission],
when 205 then 'Grant'
when 206 then 'Deny' end
from sysobjects join sysprotects on sysobjects.id=sysprotects.id
INNER JOIN sysusers ON sysprotects.uid = sysusers.uid