Introduction
I was looking at one of my servers and was caught off-guard by how many individual users had been granted rights. To make it worse I recognized a few user names as former coworkers. Clearly time for some house cleaning, now time to find a script.
The Process
I went looking in my script library for anything that might help me do this analysis faster and found a good start, a script to validate user logins. Well more like a note really, based on a conversation between Thomas LaRock (Blog|Twitter) and Tim Ford (Blog|Twitter) on Twitter about a stored procedure called sp_validatelogins that makes sure a windows login on your SQL instance matches a valid login on your domain. Thomas LaRock does a great job of documenting it here.
Next I needed to figure out what users should have been in groups. My first answer is all of them, but being realistic I decided I would identify all users that have access to the server by both individual login and group then see if the group already has the same rights the user needs or if it could / should. It turns out this was easy, a cursor of windows users with a call to xp_logininfo to check for group membership will tell me exactly what to look at, including the groups and the command to get them because I often end up pasting that into an IM.
As I started working through test data I began to notice users in databases that no longer exist on the server. Again, this one was pretty easy. A query against sys.server_principals with a right outer join to sys.database_principals quickly shows all users that exist in a database that do not have logins on the server. Adding a select to a temp table and wrapping it all in sp_MSforeachdb put it all together into a nice recordset that I could then work off of.
The Script
Here is the script I ended up with, run it in text mode rather than grid for better results. I would advise against trying to automate this any further or even taking any actions without carefully researching the impact of each change before making it. Messing with security is a quick way to get a lot of attention.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | SET NOCOUNT ON CREATE TABLE #invalid_logins CREATE TABLE #Logins account_name sysname NULL, type char(8) NULL, privilege char(9) NULL, mapped_login_name sysname NULL, permission_path sysname NULL INSERT #invalid_logins DECLARE @name sysname DECLARE csr CURSOR FAST_FORWARD READ_ONLY FOR OPEN csr FETCH NEXT FROM csr INTO @name WHILE @@FETCH_STATUS <> -1 CLOSE csr PRINT 'The following logins are no longer valid and should be removed from this server:' PRINT 'The following logins have access by login and by group and might not need access by login:' account_name, DROP TABLE #invalid_logins PRINT 'The following result set shows users that were deleted from the server but not the individual databases' db_with_orphan_login sysname, orphan_login sysname, permissions_count int EXEC sp_MSforeachdb 'USE ? INSERT #databases_with_orphan_users SELECT ''?'' AS db_with_orphan_login, dp.name, (SELECT COUNT(*) FROM sys.database_permissions WHERE grantee_principal_id = sp.principal_id) AS permissions_count FROM sys.server_principals sp RIGHT OUTER JOIN sys.database_principals dp ON sp.sid = dp.sid WHERE sp.sid IS NULL AND dp.type NOT IN (''R'') AND dp.name NOT IN (''guest'', ''sys'', ''INFORMATION_SCHEMA'') AND dp.type_desc NOT IN (''APPLICATION_ROLE'')' SELECT db_with_orphan_login, orphan_login, permissions_count DROP TABLE #databases_with_orphan_users |
Conclusion
I hope you find this script helpful. I have a feeling this script will be one of my favorites because it takes a lot of tedious analysis and boils it down to a quick hit list. Please let me know if you run into any issues with it. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.
