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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ken Simmons » Sync All Logins on a Server in a single click...

29 posts, Page 1 of 3

1 2 3 »»»

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

Rate Topic

Display Mode

Topic Options

Author

Message

KenSimmons

Posted Monday, June 02, 2008 10:12 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:07 AM
Points: 1,221, Visits: 2,614

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

Ken Simmons
http://twitter.com/KenSimmons

Post #510386

steffen.eckstein

Posted Tuesday, June 03, 2008 12:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 04, 2008 8:16 AM
Points: 2, Visits: 8

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

Post #510423

David McKinney

Posted Tuesday, June 03, 2008 12:52 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:07 AM
Points: 583, Visits: 1,609

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.

Post #510439

steffen.eckstein

Posted Tuesday, June 03, 2008 1:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 04, 2008 8:16 AM
Points: 2, Visits: 8

Hi,

that's it!!!

Thanks a lot!
Steffen

Post #510447

mohammed moinudheen

Posted Tuesday, June 03, 2008 2:13 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:29 PM
Points: 2,178, Visits: 3,599

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.

Mohammed Moinudheen

Post #510467

shepton

Posted Tuesday, June 03, 2008 2:30 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:34 AM
Points: 92, Visits: 282

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

Post #510478

Anipaul

Posted Tuesday, June 03, 2008 3:06 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 5:01 AM
Points: 4,813, Visits: 1,343

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

Post #510499

steeled

Posted Tuesday, June 03, 2008 6:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 8:15 AM
Points: 6, Visits: 79

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.

Post #510582

SQL Babe

Posted Tuesday, June 03, 2008 7:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, February 05, 2012 11:32 AM
Points: 8, Visits: 91

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

SQL Babe

Post #510620

Oleg Oransky

Posted Tuesday, June 03, 2008 7:43 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:23 AM
Points: 21, Visits: 251

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

Line 35: Incorrect syntax near 'sp_msforeachdb'.

my environment is SQL 2005 SP2.

Post #510659

« Prev Topic | Next Topic »

29 posts, Page 1 of 3

1 2 3 »»»

Permissions