Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Sync All Logins on a Server in a single click using SP_MSForEachDB Expand / Collapse
Author
Message
Posted Monday, June 2, 2008 10:12 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,220, 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
Posted Tuesday, June 3, 2008 12:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 4, 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
Posted Tuesday, June 3, 2008 12:52 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:54 AM
Points: 648, Visits: 1,876
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
Posted Tuesday, June 3, 2008 1:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

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

that's it!!!

Thanks a lot!
Steffen
Post #510447
Posted Tuesday, June 3, 2008 2:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:12 PM
Points: 2,278, Visits: 3,798
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
Posted Tuesday, June 3, 2008 2:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 5:59 AM
Points: 94, Visits: 294
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
Posted Tuesday, June 3, 2008 3:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 5,403, Visits: 1,400
Nice article .............


Post #510499
Posted Tuesday, June 3, 2008 6:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 6:38 PM
Points: 6, Visits: 88
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
Posted Tuesday, June 3, 2008 7:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 3:33 PM
Points: 10, Visits: 98
Hey Ken, speaking of Syncs...guess what we're finally rewriting...we're going with SQL CE...



SQL Babe
Post #510620
Posted Tuesday, June 3, 2008 7:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:04 AM
Points: 21, Visits: 272
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse