<div>
Any reason why you use dynamic SQL? You should be able to directly provide the username. 

<pre><code class="code-10 nolinks" id="code-20-41395321-1">DECLARE @Username VARCHAR(100)

DECLARE userlogin_cursor CURSOR FAST_FORWARD FOR
 SELECT username = name
 FROM sysusers
 WHERE issqluser = 1
 AND (sid IS NOT NULL
 AND sid &lt;&gt; 0x01)
 AND Suser_sname(sid) IS NULL -- AND name='jtang'
 ORDER BY name
 FOR READ ONLY

OPEN userlogin_cursor
FETCH NEXT FROM userlogin_cursor INTO @Username

WHILE @@FETCH_STATUS = 0
BEGIN
 -- print @username
 exec sp_change_users_login 'update_one', @username, @username
 FETCH NEXT FROM userlogin_cursor INTO @Username
END

CLOSE userlogin_cursor
DEALLOCATE userlogin_cursor
</code></pre>
And why should it make a difference if you execute as single SQL commands or as batch? A batch will (probably) stop as soon as an error occurs, so executing single SQLs seems to be better.
</div>

<div>
You should use EXEC to run a stored proc. &nbsp;SQL allows you to skip it for the first proc in a batch, &nbsp;but it's not a good idea to get in that habit. 
 
EXEC sp_change_users_login &quot;update_one&quot;, &quot;AdminProxy&quot;, &quot;AdminProxy&quot; 
EXEC sp_change_users_login &quot;update_one&quot;, &quot;CybernetUser&quot;, &quot;CybernetUser&quot; 
 
Should work fine.
</div>

<div>
basically the error is : 
 
Msg 102, Level 15, State 1, Line 2 
Incorrect syntax near 'sp_change_users_login'. 
 
one thing, if any of this login is absent, SQL server will generate an error, will the script just stop ? then we need to control the flow of the task by job steps to make it go to next step ? 
 
can we have SQL serve keep doing sp_change_users_login if any of them fail and email me the one which is failed?
</div>

<div>
MSSQL 2008 SSMS will continue processing if an execution error occurs (but not for syntax errors). 
Which suggestion caused the error message above? Your own, mine, or Scott's?
</div>

<div>
Qlemo, 
 
this one: 
 
EXEC sp_change_users_login &quot;update_one&quot;, &quot;AdminProxy&quot;, &quot;AdminProxy&quot; 
EXEC sp_change_users_login &quot;update_one&quot;, &quot;CybernetUser&quot;, &quot;CybernetUser&quot; 
 
as the user name doesn't exists.
</div>

<div>
An invalid user does not result in that error message, which is a syntax/parsing error. If the user does not exist, you get a very specific error telling you that.
</div>

<div>
Single quotes, not double. &nbsp;The values are literals not identifiers: 
 
EXEC sp_change_users_login 'update_one', 'AdminProxy', 'AdminProxy' 
EXEC sp_change_users_login 'update_one', 'CybernetUser', 'CybernetUser'
</div>

<div>
Scott, though that's correct, it indeed works with double quotes too.
</div>

<div>
ScottPletcher, Qlemo, 
 
yeah I knew that, but if use my script and let the dynamic SQL works well, we need double quote. 
 
I am not sure why but some times parser work like that, MySQL is much worse in this case! I don't like MySQL, haahhah. 
 
yeah. MSSQL much better. 
 
Qlemo: 
 
&quot;An invalid user does not result in that error message, which is a syntax/parsing error. If the user does not exist, you get a very specific error telling you that. &quot; 
 
this is the error message which I consider it is an error message: 
 

<pre><code class="code-1 nolinks" id="code-20-41398676-1">Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Login name 'AdminProxy' is absent or invalid.
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Login name 'CybernetUser' is absent or invalid.
</code></pre>
</div>

<div>
Now we are talking! Yes, those are because the user does not exist. Offhand I cannot tell whether you are in the wrong DB, or it's the global SQL login missing.
</div>

<div>
&quot;Now we are talking! &quot; 
 
huh., I post error message.. 
 
so this kind of error don't stop SQL server script /steps keep going ,right? 
 
any way to email that error out ?
</div>

<div>
For sending a mail you need to setup DB mail, use a try catch block, etc. pp. 
I presume you are doing that on the same server all the time. Then I would instead validate the existing users, and create the failing SQL Logins with default password - or ignore them. IMHO that makes more sense. 

<pre><code class="code-10 nolinks" id="code-20-41399330-1">DECLARE @Username VARCHAR(100)

DECLARE userlogin_cursor CURSOR FAST_FORWARD FOR
 SELECT username = name
 FROM sysusers
 WHERE issqluser = 1
 AND (sid IS NOT NULL
 AND sid &gt; 0x01)
 AND Suser_sname(sid) IS NULL -- AND name='jtang'
 ORDER BY name
 FOR READ ONLY

OPEN userlogin_cursor
FETCH NEXT FROM userlogin_cursor INTO @Username

WHILE @@FETCH_STATUS = 0
BEGIN
 begin try
 exec sp_change_users_login 'update_one', @username, @username
 end try
 begin catch
 print 'creating '+@username
 exec ('create login '+ @username+' with password = ''xxxx'', check_policy = off')
 exec sp_change_users_login 'update_one', @username, @username
 end catch
 FETCH NEXT FROM userlogin_cursor INTO @Username
END

CLOSE userlogin_cursor
DEALLOCATE userlogin_cursor
</code></pre>
</div>

<div>
Personally, in these situations, I just change the sid on the test server one time, then never have to re-sync again :-). &nbsp;I find that easier than having to re-sync after every restore.
</div>

<div>
ScottPletcher, 
 
that steps is common after restore production to test, and the existing userid can't match the one from production. 
 
please explain how you change the sid on the test server one time ? 
 
Qlemo, 
 
&quot;I presume you are doing that on the same server all the time. &quot; 
 
yes. as I want to automate the whole process, so this is it 
 
&quot;and create the failing SQL Logins with default password - or ignore them&quot; 
 
I prefer ignore them, that's why I just need to know by email, I just want to know how many are there. 
 
what IMHO, I forgot
</div>

<div>
In that case just collect their names in the catch block into a single variable, and then mail with xp_sendmail (<a href="https://technet.microsoft.com/en-us/library/ms189505%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396" rel="ugc">https://technet.microsoft.com/en-us/library/ms189505%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396</a>) or sp_send_dbmail (<a href="https://technet.microsoft.com/en-us/library/ms175887(v=sql.105).aspx" rel="ugc">https://technet.microsoft.com/en-us/library/ms175887(v=sql.105).aspx</a>) when finished. Both mailing approaches need some setup before you can use them.
</div>

<div>
Qlemo, 
 
&quot;use a try catch block, etc. pp.&quot; 
 
if any login missing I need to ignore it , then there will be nothing in the begin catch blocks ? 
 
and if I just need that email out, I will just need to replace your begin catch block to a email only blocks ? 
 
but how to email out the login name as well as the whole sp_change_users_login &nbsp;command, like the one I post so that I can execute/check on the command I can reapply and the failure message about that command out ? 
 
possible ? I want to do sth later on to follow up and I have enough information for me to follow up. 
 
 
ScottPletcher, 
 
the login of test and production is diff, how can your logic works ? we might not want to sync it, agree?
</div>

<div>
An empty catch block ignores any error. 
 
Re mailing: I don't understand what troubles you. You have the statement ready in the catch block, just mail it! As said, I cannot help with the mail command itself, because only you can know the exact parameters. 
 
Re Scott's script: It does no harm to have SQL logins with the same SID on production and test, unless you definitely do not want certain logins to work in test. 
If you need additional users in test not being available in production, just give them high SIDs, which won't collide with the production SIDs.
</div>

<div>
<blockquote>
ScottPletcher, 
 
the login of test and production is diff, how can your logic works ? we might not want to sync it, agree?
</blockquote>
 
Depends. &nbsp;You can match the login names for prod and test or have them be different; we have a mix of both.
</div>

<div>
Qlemo, 
 
&quot;An empty catch block ignores any error.&quot; 
yes! tks 
 
scott: 
 
&quot;--BEFORE dropping the login on the *test* instance, make sure you have: 
&nbsp;-- &nbsp;the login's current password; 
&nbsp;-- &nbsp;*all* the login's server-level permissions; 
&nbsp;-- &nbsp;the login's default database; 
&nbsp;-- &nbsp;the login's *production* sid.&quot; 
 
basically &nbsp;we will not have all login's current password. and your script just resync one users, how about we have a lot of users after DB restore to TEST platform. 
 
jtang is the only one, by your script how can I do for all users? you can see from my question that we will have more than one .
</div>

<div>
You run the script once for each user. &nbsp; If the login and user names are the same for test and prod, you only have to run this code once, after that they will automatically sync up when you do db restores.
</div>

<div>
&quot;You run the script once for each user. &nbsp;&quot; 
 
that's right! 
 
&quot;CREATE LOGIN jtang WITH 
&nbsp; &nbsp; &nbsp;PASSWORD = '&lt;enter_password_here&gt;', 
&nbsp; &nbsp; &nbsp;SID = 0x28297A8F7292704FBBE36BBFB43482D0, --&lt;&lt;-- copy this user's sid from *production* 
&nbsp; &nbsp; &nbsp;DEFAULT_DATABASE = &lt;default_db_name&gt; 
&nbsp;EXEC sp_addsrvrolemember 'jtang', 'bulkadmin' 
&nbsp;--grant other server-level permissions here 
&quot; 
sorry, how can we find out the same sid? any script for that? 
&nbsp; 
why this one: EXEC sp_addsrvrolemember 'jtang', 'bulkadmin' ? just an example right? 
 
seem not to me, I just need to add back all permission this account has but we shouldn't do this as the permission already there and that's why we just need to do this: 
 
EXEC sp_change_users_login ''UPDATE_ONE'', ''jtang'', ''jtang'' 
 
? 
 
&quot;--BEFORE dropping the login on the *test* instance, make sure you have: 
&nbsp;-- &nbsp;the login's current password; 
&nbsp;-- &nbsp;*all* the login's server-level permissions; 
&nbsp;-- &nbsp;the login's default database; 
&nbsp;-- &nbsp;the login's *production* sid. 
&nbsp;DROP LOGIN jtang&quot; 
 
 
we will not have that user's current password, this is their password. 
also it seems very hard to automate the whole thing as we are drooping and recreate it, but you will say it is a one time process. 
 
and we are going to do it for each user, which in the list from: 
 
sp_change_users_login 'report'; 
 
? 
 
seems if we can't get the password of each user we are going to recreate the login, the solution is not going to work ?
</div>

<div>
Scott's recommendation does not prevent you from having to run the report. But any user found need to be created once with the correct or an arbitrary password, or just ignored. It is no different than what you would have to do with your script. 
The difference is 
a) you run the report 
b) you decide what to do with the new accounts 
 
There is no c) You revalidate the users, as long as you do not &quot;implement&quot; the new user.
</div>

<div>
Technically you don't &quot;create&quot; the user, it still exists. &nbsp;Do not delete the existing user, because that would clear its permissions. &nbsp;Simply re-sync the existing user to the new login. 
 
Also, after one re-sync, you never have to re-sync or report again. &nbsp;Since the sid's are now the same, &nbsp;SQL will automatically sync that login and user in the future.
</div>

<div>
Qlemo, 
 
tks. but my concern is, I will not have the existing test server's user password, the recreate without answer user to reinput the password again seem impossible, there are a lot of test not in this office. 
 
this will create noise. 
 
ScottPletcher, 
 
&quot;&nbsp;Simply re-sync the existing user to the new login. 
&quot; 
 
yep, this is the key part: 
 
 

<pre><code class="code-1 nolinks" id="code-20-41407931-1">CREATE LOGIN jtang WITH 
 PASSWORD = '&lt;enter_password_here&gt;', 
 SID = 0x28297A8F7292704FBBE36BBFB43482D0, --&lt;&lt;-- copy this user's sid from *production*
 DEFAULT_DATABASE = &lt;default_db_name&gt;
 EXEC sp_addsrvrolemember 'jtang', 'bulkadmin'
 --grant other server-level permissions here
</code></pre>
 
 
again, anyway to get the SID ?
</div>

<div>
Just list the login on the production server and copy the sid column, for example: 
 
SELECT * 
FROM sys.server_principals 
WHERE name = 'jtang'
</div>

<div>
Somehow you are still missing the advantage of Scott's suggestion. 
 
You have to recreate logins anyway once in a lifetime of that server. It doesn't matter if you use your original approach or Scott's. The login needs to exist, or it does not work for that user. If the user has to exist for your tests to work, you need the password once, or a fake password. 
 
I really don't know how to say it different. But the revalidation of users is something you do if you change servers all the time - which you do not, it is just between two servers, and so you can sync a fixed set of users. If required, and that is the other point. You don't give us information about whether you need the logins for tests or not. I suppose no.
</div>

<div>
ScottPletcher, 
 
tks. 
 
Qlemo, 
 
&quot;You have to recreate logins anyway once in a lifetime of that server. It doesn't matter if you use your original approach or Scott's. The login needs to exist, or it does not work for that user. If the user has to exist for your tests to work, you need the password once, or a fake password 
 
I knew but I just want to automate the whole thing and asking them one by one on their password seems hard to work out and I just worrying about my existing approach not working very well by the script above. 
 
&quot;and so you can sync a fixed set of users.&quot; 
 
TEST server user can be diff from time to time. as we might only create user on TEST server ( which we are just did again for an application only on TEST server right now and not on production). 
 
&quot;You don't give us information about whether you need the logins for tests or not.&quot; 
 
I still need the login for TEST as the login on TEST and production is not the same always and which means, once we restore production DB to TEST, we map use id differently and that's why from time to time these command: 
 
EXEC sp_change_users_login &quot;update_one&quot;, &quot;AdminProxy&quot;, &quot;AdminProxy&quot; 
&nbsp;EXEC sp_change_users_login &quot;update_one&quot;, &quot;CybernetUser&quot;, &quot;CybernetUser&quot; 
 
can return error which say it found a user id can't map back as it don't even exists.
</div>

<div>
Arif, 
 
I found if I do this: 
 
&nbsp; &nbsp;
<pre><code class="code-1 nolinks" id="code-20-41409903-1">SET @cmd = @cmd+char(13)+ ' exec sp_change_users_login ''update_one'', ''' + @username + ''', ''' + @username + ''''
</code></pre>
 
&nbsp; and the cursor will execute the @cmd command for each loop, then we seems keep re execute the same &nbsp;sp_change_users_login for the same user over and over again ? 
 
&nbsp; so running this one already making a lot of sense: 
 

<pre><code class="code-1 nolinks" id="code-20-41409903-2">SET @cmd = 'EXEC sp_change_users_login &quot;update_one&quot;, &quot;' + @username + '&quot;, &quot;' + @username + '&quot;'
</code></pre>
</div>

<div>
Qlemo, 
 
&quot;In that case just collect their names in the catch block into a single variable, and then mail with xp_sendmail (<a href="https://technet.microsoft.com/en-us/library/ms189505%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396" rel="ugc">https://technet.microsoft.com/en-us/library/ms189505%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396</a>) or sp_send_dbmail (<a href="https://technet.microsoft.com/en-us/library/ms175887(v=sql.105).aspx" rel="ugc">https://technet.microsoft.com/en-us/library/ms175887(v=sql.105).aspx</a>) when finished. Both mailing approaches need some setup before you can use them&quot; 
 
I usually do this: 
 

<pre><code class="code-10 nolinks" id="code-20-41412360-1">set nocount on
BEGIN

/* declare @count smallint
	select @count = COUNT(*) from Sys.dm_os_waiting_tasks with(nolock) where resource_address is not null 
*/

/*if @count &gt; @tasks_waiting_treshold
	BEGIN */
		/* Declare	
			@Body varchar(8000) = @@SERVERNAME+' waiting tasks = ' + cast(@count as varchar(50)) */
 declare @BODY varchar(8000);
	 
	SELECT @BODY = @Body + CHAR(13) + CHAR(10) + CHAR(10) + s.login_name + ' ' + text
			FROM sys.dm_exec_sessions s
			INNER JOIN sys.dm_exec_requests e ON s.session_id = e.session_id
			CROSS APPLY sys.dm_exec_sql_text(e.sql_handle)
			ORDER BY s.cpu_time desc
						


		-- send email
		EXEC msdb.dbo.sp_send_dbmail 
			@profile_name = 'Account_1', 
			@recipients = @recipients, -- 
			@body = @Body,
			@subject = 'xxxx'

	/* END */
END
</code></pre>
 
to show out &nbsp;what is in the whoisactive result, but it is not going email out the error I got (user doesn't exist), how can we have that email SP email out the ERROR but not result set?
</div>

<div>
you all good, I am finally using a customized version, which modify from Qlemo
</div>

<div>
<div class="content wysiwyg-content">
Hi all, 
 
when &nbsp;I am fixing &nbsp;orphan users after restoring production DB to test server, I need to run the sp_change_users_login SP, but I think when I try to automate that by this script: 
 

<pre><code class="code-10 nolinks" id="code-10-28909079-1">DECLARE @Username VARCHAR(100),
 @cmd VARCHAR(100)

DECLARE userlogin_cursor CURSOR FAST_FORWARD FOR
 SELECT username = name
 FROM sysusers
 WHERE issqluser = 1
 AND (sid IS NOT NULL
 AND sid &lt;&gt; 0x01)
 AND Suser_sname(sid) IS NULL -- AND name='jtang'
 ORDER BY name
 FOR READ ONLY

OPEN userlogin_cursor
FETCH NEXT FROM userlogin_cursor INTO @Username

WHILE @@FETCH_STATUS = 0
 BEGIN

/* debug */
 -- SET @cmd = 'select ''sp_change_users_login &quot;update_one&quot;, &quot;' + @username + '&quot;, &quot;' + @username + '&quot; '' '
 SET @cmd = 'sp_change_users_login &quot;update_one&quot;, &quot;' + @username + '&quot;, &quot;' + @username + '&quot;'
 EXECUTE(@cmd)
	 PRINT(@cmd)
 FETCH NEXT FROM userlogin_cursor INTO @Username
 END

CLOSE userlogin_cursor
DEALLOCATE userlogin_cursor
</code></pre>
 
it will generate the following command: 
 

<pre><code class="code-1 nolinks" id="code-10-28909079-2">sp_change_users_login &quot;update_one&quot;, &quot;AdminProxy&quot;, &quot;AdminProxy&quot; 
sp_change_users_login &quot;update_one&quot;, &quot;CybernetUser&quot;, &quot;CybernetUser&quot; 
</code></pre>
 
but I find I can only execute them one by one instead of all together, any reason for that ?
</div>
</div>

Hi all,

when  I am fixing  orphan users after restoring production DB to test server, I need to run the sp_change_users_login SP, but I think when I try to automate that by this script:

(CODE)

it will generate the following command:

(CODE)

but I find I can only execute them one by one instead of all together, any reason for that ?

when sp_change_users_login command  execute one by one

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.