to find ORPHAN logins in SQL Server 2000?
-
Hi,
I would like to get a report for my LOGINS (not users) that don't have any access to any databases and are sitting idle on the server (also aren't member of any fixed server role).
Following 2 SPs will not help in this case:
1. exec sp_change_users_login 'report': It will list the orphan USERS in the DB, but I'm looking for list of orphan LOGINS.
2. sp_validatelogins: this will also not help because that lists the Windows Users that no longer exist in the Windows Environment but stillhave entries in SQL Server.
I can try writing a script but is anyone aware of any builtin SP or function??
Thanks!
-Madhur.
-
To my knowledge, you can use sp_helplogins: in the result set you have a column called Auser that means:
Yes = LoginName has an associated user name in a database.
No = LoginName does not have an associated user name.
So all the "NO" are logins wothout access to databases.
The problem can be that if a login is a member of a server role but doesn't has access to any DB in the server, sp_helplogins reports it as a NO...
See The Jenga blog
-
HI
here is the script i used to findout the orphans
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROC dbo.ShowOrphanUsers
AS
BEGIN
CREATE TABLE #Results
(
[Database Name] sysname COLLATE Latin1_General_CI_AS,
[Orphaned User] sysname COLLATE Latin1_General_CI_AS
 
SET NOCOUNT ON
DECLARE @DBName sysname, @Qry nvarchar(4000)
SET @Qry = ''
SET @DBName = ''
WHILE @DBName IS NOT NULL
BEGIN
SET @DBName =
(
SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
(
'master', 'model', 'tempdb', 'msdb',
'distribution', 'pubs', 'northwind'
 
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND name > @DBName
 
IF @DBName IS NULL BREAK
SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User]
FROM ' + QUOTENAME(@DBName) + '..sysusers su
WHERE su.islogin = 1
AND su.name <> ''guest''
AND NOT EXISTS
(
SELECT 1
FROM master..sysxlogins sl
WHERE su.sid = sl.sid
 
'INSERT INTO #Results EXEC (@Qry)
END
SELECT *
FROM #Results
ORDER BY [Database Name], [Orphaned User]
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
regards
VT
-
removed
-
Hey All,
Finally, I have written this script to find orphan logins....please validate and let me know if anythings needs to be handled...Thanks! M.
usage: EXEC tempdb..usp_orphanlogins
--------------------------------------------------
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[usp_OrphanLogins]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_OrphanLogins]
GO
USE tempdb
GO
CREATE PROCEDURE usp_OrphanLogins
AS
SET NOCOUNT ON
--**********************************************************************************
-- 1. File Name: usp_OrphanLogins.sql
-- 2. Description: Displays The Logins not mapped to any database and
-- not a member of any fixed server role
-- 3. Usage: EXEC tempdb..usp_OrphanLogins
-- 4. Modification Log
-- Ver.No. Date Author Modification
-- 0.00 06/20/2007 Madhur Agarwal Initial Version
-- 0.00 06/21/2007 Madhur Agarwal Taken care of fixed server roles
-- 0.00 06/21/2007 Madhur Agarwal Taken care of offline/suspect databases
--**********************************************************************************
DECLARE @query nvarchar(3550)
,@DBName sysname
SET @DBName = ''
--create work tables
CREATE TABLE #tb2_logins (LoginName sysname NOT Null, IsOrphan char(5)Null)
CREATE TABLE #tb1_users (LoginName sysname NOT Null, DBName sysname NOT Null)
--Only SA can run this
If is_srvrolemember('sysadmin') = 0
BEGIN
PRINT 'only SYSADMIN can EXEC the utility'
RETURN
END
--Loop thru Databases
WHILE @DBName IS NOT NULL
BEGIN --Loop A
SET @DBName = (
SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
('model','distribution', 'pubs', 'northwind')
AND name > @DBName
 
If (SELECT DATABASEPROPERTYEX(@DBName, 'status'))<>'ONLINE'
BEGIN
PRINT 'Error::'
PRINT 'The Database '+@DBName+' is not ONLINE. Exiting from the SP. Please try again later'
RETURN
END
ELSE --If
BEGIN
IF @DBName IS NULL BREAK
--Add the User info to work table
SELECT @query = '
INSERT #tb1_users (DBName, LoginName)
SELECT N' + quotename(@DBName, '''') + ',l.loginname
FROM ' + quotename(@DBName, '[') + '.dbo.sysusers u, master.dbo.syslogins l
WHERE u.sid = l.sid'
EXEC(@query)
END --Else
END --Loop A
--Checkpoint
--select * from #tb1_users
--Populate Logins Work Table
INSERT #tb2_logins (LoginName ,IsOrphan) SELECT loginname, 'Yes' FROM master.dbo.syslogins
-- Update Login Status
UPDATE #tb2_logins
SET IsOrphan = 'No'
FROM #tb2_logins tl, master.dbo.syslogins l, #tb1_users tu
WHERE
(tl.LoginName = l.LoginName and
(l.sysadmin = 1
or l.securityadmin = 1
or l.serveradmin = 1
or l.setupadmin = 1
or l.processadmin = 1
or l.diskadmin = 1
or l.dbcreator = 1
or l.bulkadmin = 1
 
)or tl.LoginName = tu.LoginName
--Display Results
SELECT * FROM #tb2_logins WHERE IsOrphan = 'Yes' ORDER BY loginname
GO
-
please replace SMILEYS with ')'
M.
-
is there a way to identify users that haven't logged in to SQL Server for n days?
-
Not readily. You can monitor logins through SQL Profiler, but I think that's about it for 2000. With 2005, there's a lot more that can do this for you.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Correct even I'm aware of only Profiler in SQL 2000. Don't know if MS has come up with something in SQl 2005.
M.
-
2005 (or is it 2008? I don't recall) has login triggers and DDL auditing, so it would be pretty easy to track "last logged in" info. Have a security audit db, throw all your info in there.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply