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

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