Sync All Logins on a Server in a single click using SP_MSForEachDB

  • Comments posted to this topic are about the item Sync All Logins on a Server in a single click using SP_MSForEachDB

  • Hi Ken,

    got a little problem running your first script.

    I got the error: "Type SYSNAME is not a defined system type."

    Any idea why this could be?

    Thanks in advance

    Steffen

  • Looks like your database server is case sensitive (like mine.)

    change SYSNAME to sysname and you should be ok.

    Also sp_msforeachdb will have to become sp_MSforeachdb

    Regards,

    David.

  • Hi,

    that's it!!!

    Thanks a lot!

    Steffen

  • Very good scripts.

    Ken, you made my task so much easier.

    I am on migration project. This article was very useful to sync up the logins.

    M&M

  • Hi Ken,

    Tried this against a couple of our dev servers + it picked up a couple of rogue logins on the SQL 2005 box but it failed with "Line 7: Incorrect syntax near 'Collate'." on SQL 2000. Does the 2000 version of sp_msforeachdb not like "Collate"?

    Thanks for the article.

    Mark

  • Nice article .............

  • Ken,

    This is the type of script that I have wanted forever! Thanks. One problem, I am not able to get it to work. I copied and pasted as is and I get the following...

    Line 35: Incorrect syntax near 'sp_msforeachdb'.

    I am assuming that the Set @SQL statement is the problem (as it is all in red).

    Any help would be great.

    BTW, my environment is SQL 2005 SP2 Std.

  • Hey Ken, speaking of Syncs...guess what we're finally rewriting...we're going with SQL CE...

    SQL Babe

  • I copied and pasted as is and I get the following...

    Line 35: Incorrect syntax near 'sp_msforeachdb'.

    my environment is SQL 2005 SP2.

  • If any one is having troubkle with this (on SQL 2005) make sure that the INSERT statement reads as follows...

    INSERT into ##TempSync EXEC sp_msforeachdb @SQL

    For some reason the ##TempSync & EXEC are run together if you just copy and paste.

    Mark

  • THANKS!!!! That did it. You're a genious.

  • It looks like the formatting gets messed up when you cut and paste. This is what I got when I cut and pasted.

    INSERT into ##TempSyncEXEC sp_msforeachdb @SQL (incorrect)

    Make sure the line reads...

    INSERT into ##TempSync

    EXEC sp_msforeachdb @SQL

  • This script reminds me of a song and goes a little something like this:

    "I've been through the desert on a horse named Dewayne"

    What's up, Ken.

    Rodney

  • Same issue on both SQL Server 2000 and 2005: syntax errors. The copy is jumbling the code and I was careful in separating it but no luck.

    update -- I managed to find the errors. Somehow the NL were lost on some of the lines and I had missed fixing a couple. But I got both scripts running. Thank you for the script.

    -- Mark D Powell --

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply