Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fix all orphaned users in a database


Fix all orphaned users in a database

Author
Message
harsha.majety
harsha.majety
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 448
Comments posted to this topic are about the item Fix all orphaned users in a database
Julien.Chappel
Julien.Chappel
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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...
Rudy Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1303
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



harsha.majety
harsha.majety
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 448
thanks rudy
harsha.majety
harsha.majety
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 448
@julien : i ran the script on sql server 2008, worked fine for me.
Rudy Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1303
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



Patrick2525
Patrick2525
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 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
Rudy Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1303
Good points Patrick



rVadim
rVadim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 2299
I use Mike Tutor's script. If someone interested it's here:

http://www.sqlservercentral.com/scripts/users/69523/
john.moreno
john.moreno
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 Visits: 1112
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].
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