November 12, 2008 at 9:49 am
Hello all, in my eternal quest to develop a better set of auditing tools, I'm creating a report to find orphaned logins and want to use sp_change_users_login. If run as a batch/in another transaction, it will generate an open transaction warning saying it can't be run when a transaction is running. In the code snippet below, the code chokes at the 'Insert into...' section.
[font="Courier New"]
exec sp_msforeachdb
'use ?;
insert into #TOrphans(UserName, UserSID)
exec sp_change_users_login @Action=''Report'';
update #Torphans
set DBName = db_name() where DBName is null
'[/font]
Anyone know of a workaround? or would I have to do something like:
[font="Courier New"]
exec sp_msforeachdb
'use ?;
select * from sysusers where name not in (select * from syslogins)
'
[/font]
Thanks.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
November 12, 2008 at 10:36 am
It would be better if you make use of cursor and do the same. One time cursor run is fine.
MJ
November 12, 2008 at 11:03 am
Okay...the proper/full workaround I came up with was:
create table #TOrphans
(
DBName varchar(128) collate SQL_Latin1_General_CP1_CI_AS,
UserName sysname collate SQL_Latin1_General_CP1_CI_AS
)
exec sp_msforeachdb
'use ?;
insert into #TOrphans(DBName, UserName)
select db_name() collate SQL_Latin1_General_CP1_CI_AS, Name collate SQL_Latin1_General_CP1_CI_AS from dbo.sysusers
where name not in (select name collate SQL_Latin1_General_CP1_CI_AS from master.dbo.syslogins) and uid < 1000
and name not in (''public'', ''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'') -- and any other exclusions here
'
select DBName, UserName from #TOrphans
drop table #TOrphans
The collations are necessary because I was getting weird collation errors when doing the select from master..syslogins. Ugly but at least stable.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
November 12, 2008 at 1:47 pm
Please add and islogin=1 to your where clause as on my installation its displaying rows for userid which is not there under sysusers and not under syslogins too(its weird but true).
Workaround is fine.
MJ
November 12, 2008 at 1:50 pm
MANU (11/12/2008)
Please add and islogin=1 to your where clause as on my installation its displaying rows for userid which is not there under sysusers and not under syslogins too(its weird but true).Workaround is fine.
MJ
Hmmm...funny, I didn't realize before that I'm catching roles as well. The Islogin=1 definitely helps.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply