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


Orphan/Inactive SQL 2005 Users


Orphan/Inactive SQL 2005 Users

Author
Message
Nishernd
Nishernd
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 70
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
Sqlchicken
Sqlchicken
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1015 Visits: 671
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
Nishernd
Nishernd
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 70
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 Smile

Thank

TG
Key DBA
Key DBA
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2803 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
Summer90
Summer90
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12897 Visits: 3899
sp_helpusers is another one that I use.
Nishernd
Nishernd
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 70
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.
Key DBA
Key DBA
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2803 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
Nishernd
Nishernd
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 70
Thank You

I will have a look at it,

I Really Appreciate It.

BigGrin
sunshine-587009
sunshine-587009
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4239 Visits: 1287
This article when the script is run shows 'sys' as an orphaned user. Be careful...

¤ §unshine ¤
Sugesh Kumar
Sugesh Kumar
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13565 Visits: 358
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
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