Orphaned Logins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Orphaned Logins

Author

Topic

dwalker79
Yak Posting Veteran

54 Posts

Posted - 2008-10-03 : 10:37:02

Does anyone know how go generate a list of SQL Server 2000 logins that don't have access to any databases? I can get the list of orphaned users that don't have a login but I now need to find logins that don't have access to any databases.

Thanks!!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-03 : 11:26:07

You can export sp_helplogins into Temp table
and query like this:

Select LoginName from #temptable
where AUser = 'No'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-03 : 13:59:32

careful because a login may be a member of sysadmin server role and have access to databases without explicitly being a user in them.

Be One with the Optimizer
TG

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-03 : 14:04:02

Yes TG that doesn't account for Server role.

You can use like this:
Select name from syslogins
where sysadmin(or other server roles...)=1

dwalker79
Yak Posting Veteran

54 Posts

Posted - 2008-10-06 : 10:01:50

Can you tell me what the syntax to export the results of sp_helplogins into a temp table would be?

Thanks!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-06 : 10:03:26

run Sp_helplogins. Create #temp table with same columns and proper datatype and use:

Insert #temp
Exec sp_helplogins

dwalker79
Yak Posting Veteran

54 Posts

Posted - 2008-10-06 : 10:16:20

I ran the following and it gave me the error: "Column name or number of supplied values does not match table definition."

set nocount on
CREATE Table #sp_helplogins
(
LoginName sysname collate database_default NOT Null,
SID varchar(85) collate database_default NOT Null,
DefDBName sysname collate database_default Null,
DefLangName sysname collate database_default Null,
AUser char(5) collate database_default Null,
ARemote char(7) collate database_default Null
)
insert into #sp_helplogins execute sp_helplogins

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-06 : 11:04:53

I guess the problem is that sp_helplogins returns multiple result sets - that is probably messing up the insert.
Here is some "slapped together" code which may help. It has not been rigorously verified so you should confirm any results you get. It returns one row per orphaned login and has a flag indicating that the user actually exists but has a different SID so it would need to be "fixed".


create table #dbusers (sid varbinary(85), name sysname)

declare @dbid int
       ,@cmd nvarchar(200)

select @dbid = min(dbid) from master..sysdatabases

--for each database
while @dbid is not null
begin
       set @cmd = 'select sid, name from ' 
       + db_name(@dbid) 
       + '..sysusers u where sid is not null and hasdbaccess = 1 and not exists (select 0 from #dbusers where sid = u.sid)'

       --insert all sids from current db that we don't already have
       insert #dbusers (sid, name)
       exec (@cmd)

       --next database
       select @dbid = min(dbid) from master..sysdatabases where dbid > @dbid
end

--report orphans
select l.loginname [Orphaned Login], case when un.name is not null then 1 else 0 end as FixOrphanedUser
from   master..syslogins l
left join #dbusers u
       on u.sid = l.sid
left join #dbusers un
        on un.name = l.loginname
where  u.sid is null
and    l.sysadmin = 0

--cleanup
drop table #dbusers

Be One with the Optimizer
TG

dwalker79
Yak Posting Veteran

54 Posts

Posted - 2008-10-06 : 11:31:05

It appears that this gives me exactly what I'm looking for. Thank you so much for your help, I really appreciate it!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-06 : 12:41:27

you're welcome - glad its working for you :)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources