We are (finally) actually upgrading our production server from 2000 to 2008r2, but there are a number of logins that 'appear' to be not needed. Is there any way to search all of the logins in a 2000 instance to see if they are mapped to a user, or have any db permissions. We found this script
that should work in 2005 and up, but I would like to clean up the logins before moving to the new server.
Code:
USE MASTER;
CREATE TABLE #dbusers (
sid VARBINARY(85))
EXEC sp_MSforeachdb
'insert #dbusers select sid from [?].sys.database_principals where type != ''R'''
SELECT name
FROM sys.server_principals
WHERE sid IN (SELECT sid
FROM sys.server_principals
WHERE TYPE != 'R'
AND name NOT LIKE ('##%##')
EXCEPT
SELECT DISTINCT sid
FROM #dbusers)
GO
DROP TABLE #dbusers
that should work in 2005 and up, but I would like to clean up the logins before moving to the new server.