Orphan/Inactive SQL 2005 Users

Question

Post reply

Orphan/Inactive SQL 2005 Users

Nishernd

Mr or Mrs. 500

Points: 561
More actions
January 4, 2008 at 6:29 am

#119918

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?
😎

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jorge Segarra SSCrazy Points: 2571 More actions · Answer 1

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 Mr or Mrs. 500 Points: 561 More actions · Answer 2

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

Key DBA SSCertifiable Points: 6029 More actions · Answer 3

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 SSC-Dedicated Points: 32891 More actions · Answer 4

Summer90

SSC-Dedicated

Points: 32891

January 9, 2008 at 11:24 am

#766339

sp_helpusers is another one that I use.

Nishernd Mr or Mrs. 500 Points: 561 More actions · Answer 5

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 SSCertifiable Points: 6029 More actions · Answer 6

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 Mr or Mrs. 500 Points: 561 More actions · Answer 7

Thank You

I will have a look at it,

I Really Appreciate It.

😀

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 8

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

¤ §unshine ¤

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 9

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

Key DBA SSCertifiable Points: 6029 More actions · Answer 10

Sunshine,

Which database did you get 'sys' as a Oprhaned User against? It is an internally developed database or a vendor supported database?

Regards,

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

vijay_shanthi23400 SSC Veteran Points: 241 More actions · Answer 11

Hi All,

I am Vijay...

I am Going to upgrade SQL 2000 to 2005(inplace upgrade)

Can any one help me regarding ,

Before upgrade do i need to dettach the System and user dbs and move the mdf, ldf location?

*Is it enough to script the users before upgrade and executing script after upgrade,?( Pls send me the scripts)

After Upgrade , Whether the DTS package is need to migrate manually?

*

Adam Bean One Orange Chip Points: 26631 More actions · Answer 12

Check out the attached ...

It will repair and or remove users as appropriate. Run it in debug to see what work would be done per user. It is pretty self explanatory through the comments I've made, but if you have any questions, let me know.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 13

vijay_shanthi23400 (8/21/2008)
Hi All,
I am Vijay...
I am Going to upgrade SQL 2000 to 2005(inplace upgrade)
Can any one help me regarding ,
Before upgrade do i need to dettach the System and user dbs and move the mdf, ldf location?
*Is it enough to script the users before upgrade and executing script after upgrade,?( Pls send me the scripts)
After Upgrade , Whether the DTS package is need to migrate manually?
*

Vijay, it is usually best to start your own thread ... but in response to your question. No, you do not need to detach the system databases if you're doing an in place upgrade. If you want to script out your logins, your best bet is sp_help_revlogin.

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 14

I would never recommend detatch and attache for system dbs. I agree, help_rev_login is the way to go.

¤ §unshine ¤