Orphan/Inactive SQL 2005 Users

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Orphan/Inactive SQL 2005 Users

19 posts, Page 1 of 2

1 2 »»

Orphan/Inactive SQL 2005 Users

Rate Topic

Display Mode

Topic Options

Author

Message

ToyoGT

Posted Friday, January 04, 2008 6:29 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 1:01 AM
Points: 51, Visits: 61

Hi People,

Need some Urgent Help,

I need to find all Inactive SQL 2005 Logins,

I tried using sp_validatelogins, and it doesn't return any results, which means one of two things,
there is no Inactive Logins, or it doesnt work...

Has anyone had similar problems, or have another solution?

Cool

Post #438905

Sqlchicken

Posted Friday, January 04, 2008 7:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:13 AM
Points: 134, Visits: 617

What database are you querying off of? Try this:

Use master
EXEC sp_validatelogins
GO

Also this command only gives you logins that no longer exist but still have access to the instance. I'll check to see if I can find something that finds accounts that haven't logged in in awhile (which is what I'm assuming you're looking for)

=============================================================
/* Backups are worthless, Restores are priceless */

Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management

Post #438941

ToyoGT

Posted Monday, January 07, 2008 6:49 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 1:01 AM
Points: 51, Visits: 61

Hey Thanks

I Have found that sp_sp_validatelogins don't work very well,
but i did find that this works quite well...

USE [DBName]
go
sp_change_users_login @Action='Report'
go

if you come across anything that might help please share with me :)

Thank

TG

Post #439544

Key DBA

Posted Tuesday, January 08, 2008 8:09 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 26, 2009 3:02 PM
Points: 515, Visits: 655

Here are some notes regarding Oprhaned Users:

-- Check for Orphaned Users for database .
USE ;
sp_change_users_login 'report';

-- If you already have a login id and password for this user,
-- fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

-- If you want to create a new login id and password for
-- this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Happy T-SQLing.

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Post #440075

Markus

Posted Wednesday, January 09, 2008 11:24 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 1,048, Visits: 2,216

sp_helpusers is another one that I use.

Post #440826

ToyoGT

Posted Thursday, January 10, 2008 6:44 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 1:01 AM
Points: 51, Visits: 61

Hey thank for the Replies Guys,

I am using the EXEC sp_change_users_login 'Report'
[Is this SP really returning all the Orphaned Users]

and it returns alot of results, now i need to delete the users,
is there any way to remove users,

is it possible to use sp_revokedbaccess

it is a live DB so i dont want to run the wrong script.

Post #441156

Key DBA

Posted Thursday, January 10, 2008 6:55 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 26, 2009 3:02 PM
Points: 515, Visits: 655

ToyoGT,

Yes, sp_revokedbaccess can be used to drop users from a database.

Here is an article that gives code to identify Windows Users and SQL Users that are orphaned, and then gives the syntax for using sp_revokedbaccess to drop the unwanted users.

Removing Orphan Users from All databases on SQL Server
By Gregory A. Larsen
http://www.databasejournal.com/features/mssql/article.php/1578941

** Note that the code will work with both SQL Server 2000 and SQL Server 2005 **

Hope This Helps

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Post #441161

ToyoGT

Posted Thursday, January 10, 2008 7:35 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 1:01 AM
Points: 51, Visits: 61

Thank You

I will have a look at it,

I Really Appreciate It.

:D

Post #441194

sunshine-587009

Posted Tuesday, February 26, 2008 8:32 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:47 PM
Points: 360, Visits: 1,072

This article when the script is run shows 'sys' as an orphaned user. Be careful...

¤ §unshine ¤

Post #460281

Sugesh Kumar

Posted Wednesday, February 27, 2008 9:46 AM

Hall of Fame

Group: General Forum Members
Last Login: Saturday, November 10, 2012 8:18 AM
Points: 3,461, Visits: 346

You can use the link below which has a script for the same.

http://www.sql-articles.com/index.php?page=Scripts/orphan_id.html

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Post #461019

« Prev Topic | Next Topic »

19 posts, Page 1 of 2

1 2 »»

Permissions