Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Upgrade this weekend, need to determine Logins

Status
Not open for further replies.

wbodger

Programmer
Apr 23, 2007
769
0
0
US
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

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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top