July 9, 2009 at 5:38 pm
In BOL for SQL 2008, under sp_change_users_login, we are supposed to stop using sp_change_users_login and instead use ALTER USER.
One of the parameters for sp_change_users_login is "Report", which:
Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login. user, login, and password must be NULL or not specified.
My question is this: what are we supposed to use to determine orphaned users?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 4, 2009 at 3:36 pm
EXEC sp_validatelogins
September 4, 2009 at 3:55 pm
This will pick up users that were created using the WITHOUT LOGIN clause, but I don't know of any easy way to differentiate them from orphaned users. And likely there are few cases where you will have those types of users.
SELECT dp.[name], dp.sid
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp
ON dp.sid = sp.sid
WHERE sp.[name] IS NULL
AND dp.is_fixed_role = 0
AND dp.name NOT IN ('public', 'dbo', 'guest', 'INFORMATION_SCHEMA', 'sys');
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply