asked
Security - Fixing poorly implemented SQL Server Logins and Users
I have recently come across a server that has been in production for over 10 years. It's evident that when it was set up, the "DBAs" didn't pay much heed to so-called best practises. I'm no DBA and I don't claim to know much about security, but questionable practises seem to have persisted for years. For example there are server logins that belong to AD individuals, there are other logins that are AD groups, then other logins that appear to be local groups. At the database user level there are yet more AD individuals - dozens of them, many of whom left the organisation years ago. I asked the current DBA what can be done to clean these up. She just shrugged her shoulders and said "We inherited this. The genie is out of the bottle. Nothing we can do. Problem is that we don't know what were going to break by getting rid of some of these old logins/users, and some stuff is too important to break." I don't buy it. This stuff must crop up all the time. I bet every competent DBA who goes into a new organisation after an incompetent one has been at the helm, will go through some "Oh *%^^&*" moments, but there must be a way of tidying these things up without bringing a business to its knees. Surely. Do you have any tips or suggestions for spring cleaning your security?
The first big question that comes to mind is whether or not these old logins/users own objects, jobs, etc. Also, there might be security rules implemented based on "old" logins that you don't realize someone is still using. Believe it or not, I've seen (very bad) practices where an account's password will be changed to one that is known, and users will access a system on a separate computer using that account because some security rules were put in place such that only that account works, and no one there now knows how to correct that. The DBA could use the following to help determine what permissions those logins and users have: EXECUTE AS USER='domain\username'; SELECT SYSTEM_USER, 'database', * FROM sys.fn_my_permissions('database_name', 'database') ; SELECT SYSTEM_USER, 'schema', * FROM sys.fn_my_permissions('schema_name', 'schema') ; SELECT SYSTEM_USER, 'object', * FROM sys.fn_my_permissions('schema_name.object_name', 'object') ; REVERT;
You could also track logins via a login trigger or trace to determine if anyone is logging in with those accounts.
15 People are following this question.
