March 6, 2018 at 11:46 am
Hi
I have script that I run to fix orphaned user accounts on a instance - If I run the script in a standards SSMS window, it runs, hits errors with particular user accounts but crucially it carries on running and fixes the orphaned user accounts it can.
I’ve put the exact TSQL in a SQL agent job, however when the job is ran, as soon as it hits an error, it stops executing and fails. It’s as if the SQL is parsed differently in a SQL Agent job?
How do I get the TSQL to carry on regardless within a SQL Agent job , so that it runs as it does when it’s executed in a SSMS Window?
Thanks.
March 6, 2018 at 11:58 am
wak_no1 - Tuesday, March 6, 2018 11:46 AMHiI have script that I run to fix orphaned user accounts on a instance - If I run the script in a standards SSMS window, it runs, hits errors with particular user accounts but crucially it carries on running and fixes the orphaned user accounts it can. I’ve put the exact TSQL in a SQL agent job, however when the job is ran, as soon as it hits an error, it stops executing and fails. It’s as if the SQL is parsed differently in a SQL Agent job?How do I get the TSQL to carry on regardless within a SQL Agent job , so that it runs as it does when it’s executed in a SSMS Window?Thanks.
What are the errors you get when you run the script in SSMS? And what is the script?
Sue
March 6, 2018 at 1:23 pm
Sue_H - Tuesday, March 6, 2018 11:58 AMwak_no1 - Tuesday, March 6, 2018 11:46 AMHiI have script that I run to fix orphaned user accounts on a instance - If I run the script in a standards SSMS window, it runs, hits errors with particular user accounts but crucially it carries on running and fixes the orphaned user accounts it can. I’ve put the exact TSQL in a SQL agent job, however when the job is ran, as soon as it hits an error, it stops executing and fails. It’s as if the SQL is parsed differently in a SQL Agent job?How do I get the TSQL to carry on regardless within a SQL Agent job , so that it runs as it does when it’s executed in a SSMS Window?Thanks.What are the errors you get when you run the script in SSMS? And what is the script?
Sue
The script:
use <db_name>
go
create table #logins (username nvarchar(max), UserSID nvarchar(max))
GO
insert into #logins
EXEC sp_change_users_login 'Report'
GO
DECLARE @username nvarchar(max);
DECLARE users_Cursor CURSOR FOR
SELECT username
FROM #logins;
OPEN users_Cursor;
FETCH NEXT FROM users_Cursor INTO @username;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'Update_One', @username, @username
FETCH NEXT FROM users_Cursor INTO @username;
END;
CLOSE users_Cursor;
DEALLOCATE users_Cursor;
GO
drop table #logins
GO
So when this is ran, it comes across the odd user account with an issue, for example:
(1 row(s) affected)
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Login name '<login_name' is absent or invalid.
If I run in SSMS, the code will carry on and fix the accounts it can, if ran with SQL Agent, it fails to carry on running when it hits the first error.
March 6, 2018 at 2:05 pm
To add, this behaviour only appears to be happen in 2008R2, I've tested in a 2012 instance and do not get the same results; the script runs within SQL Agent even if it hits errors.
March 6, 2018 at 2:35 pm
wak_no1 - Tuesday, March 6, 2018 1:23 PMSue_H - Tuesday, March 6, 2018 11:58 AMwak_no1 - Tuesday, March 6, 2018 11:46 AMHiI have script that I run to fix orphaned user accounts on a instance - If I run the script in a standards SSMS window, it runs, hits errors with particular user accounts but crucially it carries on running and fixes the orphaned user accounts it can. I’ve put the exact TSQL in a SQL agent job, however when the job is ran, as soon as it hits an error, it stops executing and fails. It’s as if the SQL is parsed differently in a SQL Agent job?How do I get the TSQL to carry on regardless within a SQL Agent job , so that it runs as it does when it’s executed in a SSMS Window?Thanks.What are the errors you get when you run the script in SSMS? And what is the script?
Sue
The script:
use <db_name>
gocreate table #logins (username nvarchar(max), UserSID nvarchar(max))
GOinsert into #logins
EXEC sp_change_users_login 'Report'
GODECLARE @username nvarchar(max);
DECLARE users_Cursor CURSOR FOR
SELECT username
FROM #logins;OPEN users_Cursor;
FETCH NEXT FROM users_Cursor INTO @username;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'Update_One', @username, @username
FETCH NEXT FROM users_Cursor INTO @username;
END;
CLOSE users_Cursor;
DEALLOCATE users_Cursor;
GOdrop table #logins
GOSo when this is ran, it comes across the odd user account with an issue, for example:
(1 row(s) affected)
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Login name '<login_name' is absent or invalid.If I run in SSMS, the code will carry on and fix the accounts it can, if ran with SQL Agent, it fails to carry on running when it hits the first error.
For that error, the login itself does not exist. Only autofix will create logins otherwise the login has to exist to use sp_change_users_login.
So...check if there is a login. You would need to match those up by SIDs as a login can be mapped to a database with a different name in the database. And for that to work you would need to change the UserSID for the temporary table to varbinary(85)
Not sure what you are doing when you hit those without logins when using SSMS.
Sue
March 6, 2018 at 2:44 pm
wak_no1 - Tuesday, March 6, 2018 2:05 PMTo add, this behaviour only appears to be happen in 2008R2, I've tested in a 2012 instance and do not get the same results; the script runs within SQL Agent even if it hits errors.
Not on 2014. The ran your script in a job - failed when there were logins that did not exist.
Sue
March 7, 2018 at 6:30 am
Sue_H - Tuesday, March 6, 2018 2:44 PMwak_no1 - Tuesday, March 6, 2018 2:05 PMTo add, this behaviour only appears to be happen in 2008R2, I've tested in a 2012 instance and do not get the same results; the script runs within SQL Agent even if it hits errors.Not on 2014. The ran your script in a job - failed when there were logins that did not exist.
Sue
It bombs out on SQL 2012 too, but still fixes the users it can - I bet if you tested again, you'd probably see that.
March 7, 2018 at 6:58 am
wak_no1 - Wednesday, March 7, 2018 6:30 AMSue_H - Tuesday, March 6, 2018 2:44 PMwak_no1 - Tuesday, March 6, 2018 2:05 PMTo add, this behaviour only appears to be happen in 2008R2, I've tested in a 2012 instance and do not get the same results; the script runs within SQL Agent even if it hits errors.Not on 2014. The ran your script in a job - failed when there were logins that did not exist.
Sue
It bombs out on SQL 2012 too, but still fixes the users it can - I bet if you tested again, you'd probably see that.
It doesn't even matter - the job fails. If you don't want failures you need to check for the logins if you are using update_one. I feel bad for anyone who has to check on failures being a "good failure or a bad failure", And it would need to be checked for every failure to check the reason for the failure.
The code can be fixed to not have errors by checking for the existence of the logins.
Sue
March 7, 2018 at 10:04 am
Sue_H - Wednesday, March 7, 2018 6:58 AMwak_no1 - Wednesday, March 7, 2018 6:30 AMSue_H - Tuesday, March 6, 2018 2:44 PMwak_no1 - Tuesday, March 6, 2018 2:05 PMTo add, this behaviour only appears to be happen in 2008R2, I've tested in a 2012 instance and do not get the same results; the script runs within SQL Agent even if it hits errors.Not on 2014. The ran your script in a job - failed when there were logins that did not exist.
Sue
It bombs out on SQL 2012 too, but still fixes the users it can - I bet if you tested again, you'd probably see that.
It doesn't even matter - the job fails. If you don't want failures you need to check for the logins if you are using update_one. I feel bad for anyone who has to check on failures being a "good failure or a bad failure", And it would need to be checked for every failure to check the reason for the failure.
The code can be fixed to not have errors by checking for the existence of the logins.Sue
I understand.
I'm trying to re-write the code so that it can handle that, the aim here is to automate the process as its ran as part of a job that brings a Mirrored database out recovery.
March 7, 2018 at 10:49 am
wak_no1 - Wednesday, March 7, 2018 10:04 AMI understand.I'm trying to re-write the code so that it can handle that, the aim here is to automate the process as its ran as part of a job that brings a Mirrored database out recovery.
Automating manual processes is a great thing - I love doing those and have done ones similar to what you are working on. Most people do it just as you are doing. You'll get it done and working.
While you are working on it, you may want to think about alternatives in the future sp_change_users_login is being deprecated. Scripting out existing logins and users in advance might be used more.
Sue
March 8, 2018 at 10:45 am
I am surprised no one here mentioned Error Handling like try catch.
Print statements and logging errors will help.
There has to something different when you are running in ssms as opposed to an agent job...
March 8, 2018 at 11:41 am
Chris Hurlbut - Thursday, March 8, 2018 10:45 AMI am surprised no one here mentioned Error Handling like try catch.
Print statements and logging errors will help.
There has to something different when you are running in ssms as opposed to an agent job...
Some type of error handling in general would be good but we aren't trying to figure out the error.
We know what the error is, what caused it and how to address it. It's how sp_change_users_login is used.
Sue
March 8, 2018 at 2:56 pm
I avoid these kinds of issues - by insuring that logins created on the mirrored system are created with the same SID that was used to create the login on the primary.
To do this - I created a SQLCMD script that is run in SSMS to create the login on the primary, capture the SID - and then create the login on the secondary.
:connect PrimaryNode
:out C:\Temp\LoginSID.sql
:setvar Login NewLogin
:setvar newPassword ^%$#@!NotNeededAccountAlreadyExists+_)(*&
:setvar uniquePassword +_)(*&ThisLoginWillNeverBeUsed!@#$%^&*()
Declare @sqlCommand nvarchar(max);
--==== Create New Login if it doesn't exist
If Not Exists (Select *
From sys.server_principals sp
Where sp.name = '$(Login)')
Begin
Set @sqlCommand = '
Create Login ' + quotename('$(Login)') + '
With Password = ' + quotename('$(uniquePassword)', char(39)) + '
, default_database = master
, check_expiration = Off
, check_policy = On;';
Execute sp_executeSQL @sqlCommand;
End
-- Further code here to set permissions if needed
--==== Grab the Login SID and create the login on the destination - if it doesn't already exist
Set Nocount On;
Declare @LoginSID varbinary(85)
, @sqlCommand nvarchar(max);
Select @LoginSID = sp.[sid]
From sys.server_principals sp
Where sp.name = '$(Login)';
--==== Assume the existing login is correct
Set @sqlCommand = '
Set Nocount On;
If Not Exists (Select *
From sys.server_principals sp
Where sp.name = ' + + quotename('$(Login)', char(39)) + ')
Begin
Create Login ' + quotename('$(Login)') + '
With Password = ' + quotename('$(newPassword)', char(39)) + '
, SID = ' + convert(varchar(85), @LoginSID, 1) + '
, default_database = [master]
, check_expiration = Off
, check_policy = On;
Raiserror(''New Login has been created on %s'', -1, -1, @@servername) With nowait;
End
Declare @LoginSID varbinary(85) = ' + convert(varchar(85), @LoginSID, 1) + ';';
Select @sqlCommand;
Go
:out stdout
:connect MirrorInstance
:r C:\Temp\LoginSID.sql
Select sp.name
, sp.[sid]
, sp.create_date
, sp.modify_date
, sp.default_database_name
From sys.server_principals sp
Where sp.[sid] = @LoginSID;
Go
!!del c:\temp\LoginSID.sql
Go
By creating the logins on the primary - granting permissions on the primary in the specific databases and then creating the same login on the secondary we can insure that the login is not orphaned if the database becomes the primary and we don't have to worry about running a script to fix orphaned users.
This also allows us to setup the secondary as read-only and those logins will have access to the secondary read-only with the available permissions. This script was written to create new logins on a primary instance of an AOAG where we need to grant access to the secondary. The variable 'uniquePassword' is utilized on the primary and the account is disabled - the variable 'newPassword' is utilized on the secondary and will be used to access the read-only database.
If the secondary becomes the primary the logins that are defined and exist on the secondary will work because the SID matches and I don't have to worry about fixing orphaned users.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 9, 2018 at 1:25 am
Chris Hurlbut - Thursday, March 8, 2018 10:45 AMI am surprised no one here mentioned Error Handling like try catch.
Print statements and logging errors will help.
There has to something different when you are running in ssms as opposed to an agent job...
that's interesting, never used try...catch but will read up on it.
March 9, 2018 at 5:25 am
wak_no1 - Tuesday, March 6, 2018 11:46 AMHiI have script that I run to fix orphaned user accounts on a instance - If I run the script in a standards SSMS window, it runs, hits errors with particular user accounts but crucially it carries on running and fixes the orphaned user accounts it can. I’ve put the exact TSQL in a SQL agent job, however when the job is ran, as soon as it hits an error, it stops executing and fails. It’s as if the SQL is parsed differently in a SQL Agent job?How do I get the TSQL to carry on regardless within a SQL Agent job , so that it runs as it does when it’s executed in a SSMS Window?Thanks.
Have you tried with a Try Catch block
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply