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

Orphan/Inactive SQL 2005 Users Expand / Collapse
Author
Message
Posted Friday, January 4, 2008 6:29 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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?


Post #438905
Posted Friday, January 4, 2008 7:22 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, July 11, 2014 8:30 AM
Points: 137, Visits: 648
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
Posted Monday, January 7, 2008 6:49 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Tuesday, January 8, 2008 8:09 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr 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
Posted Wednesday, January 9, 2008 11:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:28 AM
Points: 1,233, Visits: 2,737

sp_helpusers is another one that I use.



Post #440826
Posted Thursday, January 10, 2008 6:44 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Thursday, January 10, 2008 6:55 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr 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
Posted Thursday, January 10, 2008 7:35 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Tuesday, February 26, 2008 8:32 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:37 AM
Points: 361, Visits: 1,162
This article when the script is run shows 'sys' as an orphaned user. Be careful...

¤ §unshine ¤
Post #460281
Posted Wednesday, February 27, 2008 9:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, June 28, 2013 8:25 AM
Points: 3,461, Visits: 347
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 »

Add to briefcase 12»»

Permissions Expand / Collapse