SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to sync users and logins in SQL2005 after restore


How to sync users and logins in SQL2005 after restore

Author
Message
Gé Brander
Gé Brander
SSC Eights!
SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)

Group: General Forum Members
Points: 881 Visits: 56
Does anyone have a script that synchronizes the users and logins in a database after a restore for SQL 2005?
I used one for SQL 2000, but the domain groups are not synced, and the SQL users are. So if any one knows how to do this, please post the answer!

The script I used that works half is the following:

DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null
ORDER BY name

--cannot translate sid to existing user=orphaned

OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName

WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'

EXEC sp_change_users_login 'Update_one', @UserName, @UserName

FETCH NEXT FROM orphanuser_cur INTO @UserName
END

CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
go





Shamshad Ali
Shamshad Ali
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1438 Visits: 590

Replace your cursor query with the following, first you test your query then put for cursor, I have not yet tested your all lines of script.

SELECT @UserName = NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name

You may backup uses using Generate Script from SQL Server Management Studio and then apply script to newly moved database (same or new server).

if I did any mistake let me know.

Shamshad Ali.





Gé Brander
Gé Brander
SSC Eights!
SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)

Group: General Forum Members
Points: 881 Visits: 56
Shamshad Ali thanks for your reply,

SELECT @UserName = NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name

Should be:

SELECT NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name

In a cursor declaration a variable in the select statement is not allowed.

But in the end, this does not help. Still only the SQL logins are synced and not the Windows logins with the users in the database...

Edit: my problem is solved, but the question remains...

The database was the Microsoft CRM 3.0 database, and I solved it now, by
scripting the schema's and users, changed the scripts so, that the users
where connected tot the appropiate login's, deleted the schema's and users
and finaly recreated the users and schema's.

There should however be an easier way, because when there are objects owned
by schema's you are not able to delete the schema's.

So if anyone knows the answer, please respond.



Shamshad Ali
Shamshad Ali
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1438 Visits: 590

Thanks for pointing out my mistake, Try following- if this help out :

/*Generate the 'sp_change_users_login' statements necessary to synch all users in all databases on the server.*/

set nocount on

set quoted_identifier off

declare @dbId int,

@dbName varchar(255)

select @dbId = min(dbId) from master..sysdatabases where dbid > 3

while exists (select * from master..sysdatabases where dbid = @dbId)

begin

select @dbName = name from master..sysdatabases where dbid = @dbId

PRINT 'USE [' +@dbName+ ']'

PRINT 'GO'

exec("select 'exec sp_change_users_login ''UPDATE_ONE'',''' +name+ ''',''' +name+ ''''

from [" +@dbName+ "]..sysusers where issqluser = 1 and status = 2 and uid > 2")

PRINT 'GO'

PRINT ''

select @dbId = min(dbId) from master..sysdatabases where dbid > @dbId

end

USE master

set nocount off


Shamshad Ali





Gé Brander
Gé Brander
SSC Eights!
SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)

Group: General Forum Members
Points: 881 Visits: 56
Thanks, but according BOL:

Windows groups and Windows users
are not reconnected. (BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/11eefa97-a31f-4359-ba5b-e92328224133.htm
sp_change_users_login cannot be used with Windows logins.
)

So your script probably won't work, because you use the sp_change_users_login stored procedure.

I will try later.



Philip Yale-193937
Philip Yale-193937
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 253
Why don't you just generate a script on the SQL2000 server that drops/recreates all the Windows users in each database, and then run this script against the 2005 server? Assuming all the logins already exist in the master database this should remap them all (unless I'm misunderstanding something?)
Gé Brander
Gé Brander
SSC Eights!
SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)

Group: General Forum Members
Points: 881 Visits: 56
Can you drop users when they are connected to a schema?
Can you drop a schema that owns objects?

To both questions the answer was 'No' for what I know. But I may be incorrect.

If I am right, then dropping users is not an option. Then you need to sync the users and logins. This was always possible for SQL logins and Windows logins in SQL Server 2000 with the 'sp_change_users_login' stored procedure. But for some reason, Microsoft decided to change that stored procedure that it only works for SQL Server logins in SQL Server 2005.
So the question remains:
How to do a sync with Windows logins in SQL Server 2005?...



Gé Brander
Gé Brander
SSC Eights!
SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)

Group: General Forum Members
Points: 881 Visits: 56
Does anyone has an answer to this question yet?



mishka-723908
mishka-723908
SSC-Addicted
SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)

Group: General Forum Members
Points: 441 Visits: 245
Did you get an answer to this?
Gé Brander
Gé Brander
SSC Eights!
SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)

Group: General Forum Members
Points: 881 Visits: 56
Not yet, but I will investigate this in SQL 2008, maybe it is possible there.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search