Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


correct sytax for sp_msforeachdb SQL Server 2005

Posted on 2011-04-27
Medium Priority
Last Modified: 2013-11-05
I am trying to use the sp_msforeachdb stored procedure to execute the following commands and I cannot get the sysntax correct.  can anyone help?

When I issue this:
sp_msforeachdb 'EXEC dbo.sp_changedbowner @loginame = N'DOMAIN\DomainUser', @map = false'

Open in new window

I get this:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DOMAIN'.

and when I issue this:
sp_msforeachdb 'EXEC sp_change_users_login 'Auto_fix', 'DOMAIN\DomainUser''

Open in new window

i get this:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Auto_fix'.
Question by:itsonlyme4

Accepted Solution

LCSandman8301 earned 2000 total points
ID: 35476914
you need to escape your quotes
and use the database wildcard to have the query run in the right environment
issue this:

 sp_msforeachdb 'EXEC ?.dbo.sp_change_users_login ''Auto_fix'', ''DOMAIN\DomainUser'''

sp_msforeachdb 'EXEC ?.dbo.sp_changedbowner @loginame = N''DOMAIN\DomainUser'', @map = false'

Author Comment

ID: 35477016
Awesome!  Thank you!

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Screencast - Getting to Know the Pipeline
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question