CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2008-r2ssms
Subhash
Subhash

Reputation: 33

How to map all the orphan users in sql server management studio express 2008

my target was to moved all the mssql databases from sql server management studio express 2005 server to sql server management studio express 2008 server. For this i first created the databases and the users in the new server via plesk panel. I tried logging in to the database with the created users and was able to login without any issues. But after i restore the database from the old server i was not able to login to the same database. It throws the following error:

Create failed for User 'USER_NAME'. (Microsoft.SqlServer.Smo) User, group, or role 'USER_NAME' already exists in the current database. (Microsoft SQL Server, Error: 15023)

I googled it and found the following sql query which fixed the problem, but one database at a time. Here is the query syntax

USE DB_NAME

GO
EXEC sp_change_users_login 'update_one', 'USER_NAME', 'LOGIN_NAME’
GO

I have 100s of database and i can't do this manually for all the database. My question is.. is there any alternative to map all the orphan users to their databases?

Update - **
**Cause of this issue

So as far as my research goes, the issue is with the SID ( ) override after restoring the database from the source server.

Initially while creating new logins it will be assigned with a SID and once we restore the database from source server, the login will be having the new SID assigned to it by the new server and the database will have the old SID from the source server which causes the SID mismatch between the login and the database. To resolve this we use the above syntax to override the SID in the database with the SID of the login user, that way both database and login will have the same SID.

Reference: here

Upvotes: 0

Views: 3083

Answers (2)

Stijn Wynants
Stijn Wynants

Reputation: 105

You can iterate through your databases using the sys.databases table. If your statement is correct and it fixes your problem for one database, this should do it for all you databases and for all users

create table testtable
(
tid int identity(1,1) not null,
sqlstatement varchar(max) null
)


go
DECLARE @DBNAME as varchar(100)
declare @sql2 as varchar(MAX)
declare @usestat as Nvarchar(max)
declare @update as varchar(10)
declare @usern as varchar(10)
declare @loginn as varchar(10)
declare @quote as varchar(10)

DECLARE @username varchar(25)
SET @update = 'update_one '
set @usern = 'USER_NAME '
set @loginn = 'LOGIN_NAME '
set @quote = ''''
DECLARE db_cursor CURSOR
    FOR select name from sys.databases where database_id >4
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql2 = 'USE '+@DBNAME
------------------
set @usestat = @sql2
set @usestat = ' declare @quote2 as varchar(10) set @quote2 = '''''''' DECLARE @username as varchar(MAX) DECLARE @Statement as varchar(max) DECLARE fixusers CURSOR FOR SELECT UserName = name FROM '+@DBNAME+'.sys.sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null ORDER BY name OPEN fixusers FETCH NEXT FROM fixusers INTO @username WHILE @@FETCH_STATUS = 0 BEGIN SET @statement ='+@quote+@sql2+' GO EXEC sp_change_users_login'+@quote+'+@quote2+'+@quote+'update_one'+@quote+'+@quote2+'+@quote+','+@quote+'+@quote2+@username+@quote2+'+@quote+','+@quote+'+@quote2+@username+@quote2+'+@quote+' GO'+@quote+' print @statement FETCH NEXT FROM fixusers INTO @username END CLOSE fixusers DEALLOCATE fixusers'
insert into testtable (sqlstatement)
values(@usestat)
-----------------
FETCH NEXT FROM db_cursor INTO @dbname
end
CLOSE db_cursor
DEALLOCATE db_cursor
go
declare @statement2 as varchar(max)
declare @tidcount as int
set @tidcount = 1
while @tidcount <= (select count(*) from testtable)
BEGIN
set @statement2 = (select sqlstatement from testtable where tid = @tidcount)
exec (@statement2)
set @tidcount = @tidcount + 1
END

This will give you your script in the output. This will be done for all user defined databases

Upvotes: 1

Hlin
Hlin

Reputation: 134

you also can use SSIS transfer logins(transfer login task)from old server to new server

Upvotes: 0

Related Questions