SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
KenSimmons
KenSimmons
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1608 Visits: 2614
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
steffen.eckstein
steffen.eckstein
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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
David McKinney
David McKinney
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1285 Visits: 2090
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.
steffen.eckstein
steffen.eckstein
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 8
Hi,

that's it!!!

Thanks a lot!
Steffen
M&M
M&M
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3539 Visits: 3906
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
shepton
shepton
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 313
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



Anipaul
Anipaul
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7269 Visits: 1407
Nice article .............



steeled
steeled
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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.
karlakay22
karlakay22
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 100
Hey Ken, speaking of Syncs...guess what we're finally rewriting...we're going with SQL CE...

SQL Babe
Oleg Oransky
Oleg Oransky
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search