August 2, 2008 at 9:00 pm
Hi Folks,
I have restored a SQL2000 db to a SQL2005 db I now want to get a list of orphaned users and fix them up. Documentation I found tells me this will do the trick of finding them.
EXEC sp_change_users_login 'Report'
When I run this on the db though nothing shows. I know of at least one user who doesn't have a login set in SQL which is using windows authentication. The login is a member of Domain Users and nothing else but this is not setup in the logins either. Please help!
August 2, 2008 at 9:39 pm
Here is what I do to find orphaned users and sync the logins.
http://qa.sqlservercentral.com/articles/Log+Shipping/63028/
Remember you need to have the logins created in the master database in order to be able to sync them.
You will need to copy the code from here because it does not copy correctly from the article.
http://qa.sqlservercentral.com/Forums/Topic510386-1306-2.aspx#bm510903
August 2, 2008 at 9:52 pm
I have altered the script to list all users in all databases that do not have a matching user in the master database.
DECLARE @Collation varchar(100)
DECLARE @SQL VARCHAR(2000)
CREATE TABLE ##TempSync
(
DB_NME Varchar(50),
DBUserName varchar(50),
SysLoginName varchar(50)
)
SELECT @Collation = CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))
SET @SQL = 'USE [?]
SELECT ''?'' DB_NME,
A.name DBUserName,
B.loginname SysLoginName
FROM sysusers A
LEFT JOIN master.dbo.syslogins B
ON A.name Collate ' + @Collation + ' = B.Name
JOIN master.dbo.sysdatabases C
ON C.Name = ''?''
WHERE issqluser = 1
AND (B.sid IS NULL AND A.sid <> 0x0)
AND suser_sname(A.sid) IS NULL
ORDER BY A.name'
INSERT into ##TempSync
EXEC sp_msforeachdb @SQL
SELECT * FROM ##TempSync
DROP TABLE ##TempSync
August 2, 2008 at 10:01 pm
thanks Ken.. you helped me out heaps
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply