May 28, 2019 at 6:23 pm
I need to run the same command on all DBs (75 DBs). So I built this query which works fine however, many DBs on this server are not online. So this works
DECLARE @cmd VARCHAR(1000)
SELECT @cmd = 'declare @user varchar(50)
select @user = name FROM sysusers
WHERE name NOT IN (''public'',''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'')
AND name NOT LIKE ''db_%''
use [?] EXEC sp_change_users_login ''Auto_Fix'', @user'
EXEC sp_MSforeachdb @cmd
This doesn't work when I include system DBs and DBs which are not online.
DECLARE @cmd VARCHAR(1000)
SELECT @cmd = 'declare @user varchar(50)
select @user = name FROM sysusers
WHERE name NOT IN (''public'',''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'')
AND name NOT LIKE ''db_%''
IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'',''BusinessIntelligence'')
BEGIN
use [?] EXEC sp_change_users_login ''Auto_Fix'', @user'
EXEC sp_MSforeachdb @cmd
Getting syntax error around @User. Any help is highly appreciated.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 28, 2019 at 6:58 pm
Presumably, you mean a run-time syntax error, because I see no other syntax error there?
Which specific command generates the error?
Can the select @user part of the query ever return more than one row? Or no rows?
Note also that you should be using ALTER USER rather than sp_change_users_login, which is on the way out.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 28, 2019 at 7:21 pm
You are missing an "END" after your BEGIN.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 28, 2019 at 8:03 pm
Yep, I was missing an END. That fixed the issue. Thanks!
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply