December 16, 2009 at 8:20 am
Hi,
I am looking for an easy way to find users that have no database associated with them. I have found orphaned user scripts that list out the users in a database that don't have an associated login. This is the opposite of that.
We had an old application that required SQL users for each account. This has been retired and removed and I would like to remove the logins that belonged to that app. Over the years it has been hundreds of users so an automated process would be best to identify them. This is a shared server so some logins were also used for other applications, in which case I wouldn't want to remove them.
thanks,
Brian
December 16, 2009 at 8:44 am
You'd have to scan all databases for login mappings and then look for something that didn't have a match. I might create a table, load in matches from each database with sp_MSforeachdb and then look for logins that aren't in that table.
December 16, 2009 at 8:45 am
I don't have 2000 to run this against, but something like this should work:
CREATE TABLE #logins
(
sid VARBINARY(MAX),
login_name NVARCHAR(100),
database_name sysname
)
INSERT INTO #logins
EXEC sp_MSforeachdb @command1 = N'SELECT
SL.sid,
SL.name,
''?''
FROM
syslogins AS SL LEFT JOIN
?.dbo.sysusers SU ON
SL.sid = SU.sid
WHERE
SU.sid IS NULL'
SELECT
*
FROM
syslogins SL LEFT JOIN
#logins L
ON SL.sid = L.sid
WHERE
L.sid IS NULL
DROP TABLE #logins
You'll also want to check that the logins are not in one of the fixed server roles before the
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 16, 2009 at 9:09 am
Jack,
I made some changes to get this to run on 2000.
CREATE TABLE #logins
(
sid VARBINARY(4000),
login_name NVARCHAR(100),
database_name sysname
)
INSERT INTO #logins
EXEC sp_MSforeachdb
@command1 = N'SELECT SL.sid , SL.name, ''[?]''
FROM syslogins AS SL LEFT JOIN
[?].dbo.sysusers SU ON
SL.sid = SU.sid
WHERE
SU.sid IS NULL'
SELECT *
FROM
syslogins SL LEFT JOIN
#logins L
ON SL.sid = L.sid
WHERE
L.sid IS NULL
DROP TABLE #logins
I added the brackets to handle dashes in names. 2000 doesn't have the MAX datatypes so I changed that. The sid is not null on any entries.
Thanks, Brian
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply