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 12»»

Fix all orphaned users in a database Expand / Collapse
Author
Message
Posted Tuesday, November 23, 2010 9:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 2:48 PM
Points: 9, Visits: 435
Comments posted to this topic are about the item Fix all orphaned users in a database
Post #1025649
Posted Tuesday, November 23, 2010 9:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 18, 2011 8:08 PM
Points: 10, Visits: 44
I ran the piece of code published under the title: "Fix all orphaned users in a database". It came back with a number of syntax errors. I fiddled with it and managed to run it. The environment I am using Windows 2008 R2 Server, SQL Server 2008 R2. And this is not the first time happening that SQLServerCentral.com publishes "bugy" codes. It would be nice that scripts or solutions published on the SQLServerCentral.com are debugged before publication.

regards
Julien
PS: The majority of the SQL Server solutions are good and I got a lot of help in taking many of these ideas into my solution. Only those are the annoying minorities...
Post #1025652
Posted Wednesday, November 24, 2010 6:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 315, Visits: 1,113
Thanks for the code! I've been using a script created by Gregory A. Larsen which I have modified to better suite my needs.

As for "buggy" code. Sometime we write scripts that work well in our own environments and it may not run properly in others. This is not the responsibility of this site to see if ALL the code provide works perfectly. It would take them too long and need more staff to verify everything.

Here is my recommendation. Use the scripts and modify it to suite your environment. It's free so really you can't complain. As a good DBA, you should try to modify the code so that 1) you better understand it, 2) you make it personalized to your environment and 3) you can help by provide your code here for others to see.

If you don't like the script you can also rate it here. You can even submit your own code/articles for others to see and criticize. It's not easy publishing anything as you will always find someone who hates it.

Thanks to everyone who submits code, articles, and help as these things make this site the best in the SQL world!

Just my 2 cents worth.

Rudy



Post #1025864
Posted Wednesday, November 24, 2010 7:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 2:48 PM
Points: 9, Visits: 435
thanks rudy
Post #1025906
Posted Wednesday, November 24, 2010 7:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 2:48 PM
Points: 9, Visits: 435
@julien : i ran the script on sql server 2008, worked fine for me.
Post #1025907
Posted Wednesday, November 24, 2010 7:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 315, Visits: 1,113
No thanks needed as I'm not defending anyone. One thing I don't mention is that when submitting code the author should do his/her best to ensure that it is working.

Now back the this code. Your script will re add users that are orphaned but what if they don't exist? Would your script remove them from the database?

I would modify your script so that it checks if the login exists. If not it should be removed from the database.

Thanks,

Rudy





Post #1025932
Posted Wednesday, November 24, 2010 9:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 1, 2013 10:16 AM
Points: 117, Visits: 180
It would be helpful if the Author added comments to the article and code, including what version he/she created this on and on what versions it is known to work. In this case its a pretty simple script, yet what sp_change_users_login does and how it works is still kind of vague. Of course I can google it, but it would be good if the Author addressed that, since its the backbone of the script.

BTW... msdn states that sp_change_users_login will not be used in future releases and advises not to include in new dev work
Post #1025989
Posted Wednesday, November 24, 2010 9:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 315, Visits: 1,113
Good points Patrick


Post #1026021
Posted Wednesday, November 24, 2010 11:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:16 PM
Points: 990, Visits: 2,223
I use Mike Tutor's script. If someone interested it's here:

http://www.sqlservercentral.com/scripts/users/69523/

Post #1026074
Posted Wednesday, November 24, 2010 11:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 4:18 PM
Points: 347, Visits: 1,070
Patrick2525 (11/24/2010)
It would be helpful if the Author added comments to the article and code, including what version he/she created this on and on what versions it is known to work. In this case its a pretty simple script, yet what sp_change_users_login does and how it works is still kind of vague. Of course I can google it, but it would be good if the Author addressed that, since its the backbone of the script.

BTW... msdn states that sp_change_users_login will not be used in future releases and advises not to include in new dev work


sp_change_users_login isn't something that you would include in your work, it is something you would use to DO your work.

That said, if you're running 2005 SP2 or latter, you might want to use ALTER USER [user] WITH LOGIN = [user].
Post #1026081
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse