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.
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.
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.
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
DECLARE @name sysname
DECLARE csr CURSOR FAST_FORWARD READ_ONLY FOR
FROM sys.syslogins sl
LEFT OUTER JOIN #invalid_logins il
ON sl.name = il.name
WHERE sl.isntname = 1
AND sl.isntgroup = 0
AND il.name IS NULL
FETCH NEXT FROM csr INTO @name
WHILE @@FETCH_STATUS <> -1
EXEC xp_logininfo @acctname=@name, @option='all'
FETCH NEXT FROM csr INTO @name
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:'
SELECT COUNT(*) AS num_of_groups,
REPLACE(RTRIM(( SELECT permission_path + ' '
WHERE account_name = l.account_name
FOR XML PATH('')
)), ' ', ', ') AS group_names,
'EXEC xp_logininfo @acctname=' + QUOTENAME(account_name) + ', @option=''all''' AS command_to_see_groups
FROM #Logins l
GROUP BY account_name,
HAVING COUNT(*) > 1
ORDER BY num_of_groups DESC
DROP TABLE #invalid_logins
DROP TABLE #Logins
PRINT 'The following result set shows users that were deleted from the server but not the individual databases'
CREATE TABLE #databases_with_orphan_users
EXEC sp_MSforeachdb 'USE ?
SELECT ''?'' AS db_with_orphan_login,
(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'')'
DROP TABLE #databases_with_orphan_users
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.
Thanks! Ran it once, didn’t have anything of note pop up. But, I’ve saved it off for future use.
Glad to hear it worked for you. Nice work keeping your server clean!
Pingback: SQL 2005 System Stored Procedures Poster | SQLRockstar
Nice script! Got turned onto it by another DBA in our group when I told him about a stored proc to find permissions on database objects (sp_helprotect)
Anyway I did find an error when I ran it.
Lines 36 WHILE @@FETCH_STATUS <> -1
I had to change it to “”
and line 68 HAVING COUNT(*) > 1
Changed to “>”
No big deal, just thought I would drop a line.
Thanks for pointing that out! I have had a number of posts that did that although I am not 100% sure why. I fixed the post now. I guess I will have to spend some time tonight reviewing my posts again to see how many others have that now.