Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to sync users and logins in SQL2005 after restore Expand / Collapse
Author
Message
Posted Wednesday, May 31, 2006 6:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 13, 2011 11:45 AM
Points: 221, Visits: 55
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




Post #283839
Posted Wednesday, May 31, 2006 6:49 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, 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.

 




Post #283848
Posted Wednesday, May 31, 2006 7:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 13, 2011 11:45 AM
Points: 221, Visits: 55
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.



Post #283853
Posted Thursday, June 1, 2006 6:59 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, 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.*/

</P></SPAN><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Courier New'; mso-no-proof: yes">set</SPAN><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><FONT color=#000000> nocount </FONT><SPAN style="COLOR: blue">on<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p></SPAN></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Courier New'; mso-no-proof: yes">set</SPAN><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><FONT color=#000000> quoted_identifier </FONT><SPAN style="COLOR: blue">off<o:p></o:p></SPAN></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><FONT color=#000000><SPAN style="mso-spacerun: yes">&nbsp;</SPAN><o:p></o:p></FONT></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Courier New'; mso-no-proof: yes">declare</SPAN><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><FONT color=#000000> @dbId </FONT><SPAN style="COLOR: blue">int</SPAN><SPAN style="COLOR: gray">,<o:p></o:p></SPAN></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><FONT color=#000000><SPAN style="mso-tab-count: 1">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>@dbName varchar</FONT><SPAN style="COLOR: gray">(</SPAN><FONT color=#000000>255</FONT><SPAN style="COLOR: gray">)<o:p></o:p></SPAN></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; COLOR: gray; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><o:p>&nbsp;</o:p></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Courier New'; mso-no-proof: yes">select</SPAN><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><FONT color=#000000> @dbId </FONT><SPAN style="COLOR: gray">=</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: fuchsia">min</SPAN><SPAN style="COLOR: gray">(</SPAN><FONT color=#000000>dbId</FONT><SPAN style="COLOR: gray">)</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: blue">from</SPAN><FONT color=#000000> master</FONT><SPAN style="COLOR: gray">..</SPAN><SPAN style="COLOR: green">sysdatabases</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: blue">where</SPAN><FONT color=#000000> dbid </FONT><SPAN style="COLOR: gray">&gt;</SPAN><FONT color=#000000> 3<o:p></o:p></FONT></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><o:p><FONT color=#000000>&nbsp;</FONT></o:p></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Courier New'; mso-no-proof: yes">while</SPAN><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><FONT color=#000000> </FONT><SPAN style="COLOR: gray">exists</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: gray">(</SPAN><SPAN style="COLOR: blue">select</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: gray">*</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: blue">from</SPAN><FONT color=#000000> master</FONT><SPAN style="COLOR: gray">..</SPAN><SPAN style="COLOR: green">sysdatabases</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: blue">where</SPAN><FONT color=#000000> dbid </FONT><SPAN style="COLOR: gray">=</SPAN><FONT color=#000000> @dbId</FONT><SPAN style="COLOR: gray">)<o:p></o:p></SPAN></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><SPAN style="mso-spacerun: yes"><FONT color=#000000>&nbsp;&nbsp; </FONT></SPAN><SPAN style="COLOR: blue">begin<o:p></o:p></SPAN></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><SPAN style="mso-tab-count: 1"><FONT color=#000000>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT></SPAN><SPAN style="COLOR: blue">select</SPAN><FONT color=#000000> @dbName </FONT><SPAN style="COLOR: gray">=</SPAN><FONT color=#000000><SPAN style="mso-spacerun: yes">&nbsp; </SPAN>name </FONT><SPAN style="COLOR: blue">from</SPAN><FONT color=#000000> master</FONT><SPAN style="COLOR: gray">..</SPAN><SPAN style="COLOR: green">sysdatabases</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: blue">where</SPAN><FONT color=#000000> dbid </FONT><SPAN style="COLOR: gray">=</SPAN><FONT color=#000000> @dbId<o:p></o:p></FONT></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><SPAN style="mso-tab-count: 1"><FONT color=#000000>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT></SPAN><SPAN style="COLOR: blue">PRINT</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: red">'USE ['</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: gray">+</SPAN><FONT color=#000000>@dbName</FONT><SPAN style="COLOR: gray">+</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: red">']'<o:p></o:p></SPAN></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><SPAN style="mso-tab-count: 1"><FONT color=#000000>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT></SPAN><SPAN style="COLOR: blue">PRINT</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: red">'GO'<o:p></o:p></SPAN></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><SPAN style="mso-tab-count: 1"><FONT color=#000000>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT></SPAN><SPAN style="COLOR: blue">exec</SPAN><SPAN style="COLOR: gray">(</SPAN><FONT color=#000000>"select 'exec sp_change_users_login ''UPDATE_ONE'',''' +name+ ''',''' +name+ ''''<o:p></o:p></FONT></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><FONT color=#000000><SPAN style="mso-spacerun: yes">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>from [" </FONT><SPAN style="COLOR: gray">+</SPAN><FONT color=#000000>@dbName</FONT><SPAN style="COLOR: gray">+</SPAN><FONT color=#000000> "]..sysusers where issqluser = 1 and status = 2 and uid &gt; 2"</FONT><SPAN style="COLOR: gray">)<o:p></o:p></SPAN></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><SPAN style="mso-tab-count: 1"><FONT color=#000000>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT></SPAN><SPAN style="COLOR: blue">PRINT</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: red">'GO'<o:p></o:p></SPAN></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><SPAN style="mso-tab-count: 1"><FONT color=#000000>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT></SPAN><SPAN style="COLOR: blue">PRINT</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: red">''<o:p></o:p></SPAN></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><SPAN style="mso-tab-count: 1"><FONT color=#000000>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT></SPAN><SPAN style="COLOR: blue">select</SPAN><FONT color=#000000> @dbId </FONT><SPAN style="COLOR: gray">=</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: fuchsia">min</SPAN><SPAN style="COLOR: gray">(</SPAN><FONT color=#000000>dbId</FONT><SPAN style="COLOR: gray">)</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: blue">from</SPAN><FONT color=#000000> master</FONT><SPAN style="COLOR: gray">..</SPAN><SPAN style="COLOR: green">sysdatabases</SPAN><FONT color=#000000> </FONT><SPAN style="COLOR: blue">where</SPAN><FONT color=#000000> dbid </FONT><SPAN style="COLOR: gray">&gt;</SPAN><FONT color=#000000> @dbId<o:p></o:p></FONT></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><SPAN style="mso-spacerun: yes"><FONT color=#000000>&nbsp;&nbsp; </FONT></SPAN><SPAN style="COLOR: blue">end<o:p></o:p></SPAN></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><o:p>&nbsp;</o:p></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none"><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Courier New'; mso-no-proof: yes">USE</SPAN><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><FONT color=#000000> master<o:p></o:p></FONT></SPAN></P><P class=MsoNormal style="MARGIN: 0in 0in 10pt"><SPAN style="FONT-SIZE: 10pt; COLOR: blue; LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; mso-no-proof: yes">set</SPAN><SPAN style="FONT-SIZE: 10pt; LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><FONT color=#000000> nocount </FONT><SPAN style="COLOR: blue">off</SPAN></SPAN></P><SPAN style="FONT-SIZE: 10pt; LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; mso-no-proof: yes"><SPAN style="COLOR: blue"><P>

 

Shamshad Ali




Post #284187
Posted Thursday, June 1, 2006 7:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 13, 2011 11:45 AM
Points: 221, Visits: 55
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.



Post #284203
Posted Monday, June 5, 2006 10:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 6:40 PM
Points: 241, 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?)
Post #284971
Posted Tuesday, June 6, 2006 1:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 13, 2011 11:45 AM
Points: 221, Visits: 55
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?...



Post #285095
Posted Wednesday, September 5, 2007 3:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 13, 2011 11:45 AM
Points: 221, Visits: 55
Does anyone has an answer to this question yet?


Post #396382
Posted Tuesday, August 12, 2008 5:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
Did you get an answer to this?
Post #551497
Posted Wednesday, August 13, 2008 12:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 13, 2011 11:45 AM
Points: 221, Visits: 55
Not yet, but I will investigate this in SQL 2008, maybe it is possible there.


Post #551622
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse