Post reply

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

  • June 2, 2008 at 10:12 pm

    #207992

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

  • June 3, 2008 at 12:14 am

    #823418

    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

  • June 3, 2008 at 12:52 am

    #823431

    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.

  • June 3, 2008 at 1:13 am

    #823437

    Hi,

    that's it!!!

    Thanks a lot!

    Steffen

  • June 3, 2008 at 2:13 am

    #823452

    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

  • June 3, 2008 at 2:30 am

    #823463

    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

  • June 3, 2008 at 3:06 am

    #823481

    Nice article .............

  • June 3, 2008 at 6:19 am

    #823549

    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.

  • June 3, 2008 at 7:04 am

    #823578

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

    SQL Babe

  • June 3, 2008 at 7:43 am

    #823612

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

    Line 35: Incorrect syntax near 'sp_msforeachdb'.

    my environment is SQL 2005 SP2.

  • June 3, 2008 at 7:53 am

    #823619

    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

  • June 3, 2008 at 7:57 am

    #823620

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

  • June 3, 2008 at 8:07 am

    #823626

    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

  • June 3, 2008 at 9:04 am

    #823685

    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

  • June 3, 2008 at 11:49 am

    #823784

    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 29 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy