Join Sysusers to Sysdatabases?

[Catadmin]

Help!

I'm working on a stored procedure to pull back all users by database. Unfortunately, I can't find a good consistent way to connect the users to the databases. I've tried going through master.dbo.syscacheobjects and then I tried going through master.dbo.sysprocesses. The problem with going that route is that it only pulls users currently connected or that have processes running.

Finally, I came up with a cursor that is supposed to cycle through each database and pull up the users in each db's SysUsers table, but it pulls up ALL users, regardless of whether they have access to the current database, and appends in the current database name.

I'm at my wits end. Any help would be greatly appreciated. Here's the code:

Create table #DBPermits(LoginName varchar(75), DatabaseName varchar(75),
DatabaseRole varchar(75), objectName varchar(300), HasDBAccess bit,
Action varchar(30), 
AccessType varchar(15), CreateDate smalldatetime, 
UpdateDate smalldatetime, WindowsGroup char(3), WindowsUser char(3),
SQLUser char(3), SQLRole char(3), AppRole char(3))

Declare @DBName varchar(75)
Declare DBCur CURSOR GLOBAL for 
     Select Name from master.dbo.sysdatabases
     Order by Name

Open DBCur

FETCH NEXT from DBCur into @DBName

WHILE @@Fetch_Status = 0

BEGIN

Insert into #DBPermits(LoginName, DataBaseName, DatabaseRole, objectName,
HasDBAccess, Action, AccessType, CreateDate, UpdateDate, WindowsGroup, 
WindowsUser, SQLUser, SQLRole, AppRole)
(select Distinct sysusers.name as LoginName, @DBName,
 Case when B.Name IS NULL then
    	'No Database Roles'
 else
	B.Name
 end DatabaseRole,
sysobjects.name as ObjectName, sysusers.hasdbaccess,
    case when action = 193 then
        'SELECT'
    when action = 195 then
        'INSERT'
    when action = 196 then
        'DELETE'
    when action = 197 then
        'UPDATE'
    when action = 224 then
        'EXECUTE'
    when action = 178 then
        'CREATE FUNCTION'
    when action = 198 then
 	'CREATE TABLE'
    when action = 203 then
	'CREATE DATABASE' 
    when action = 207 then
	'CREATE VIEW'
    when action = 222 then
	'CREATE PROCEDURE'
    when action = 228 then
	'BACKUP DATABASE'
    when action = 233 then
	'CREATE DEFAULT'
    when action = 235 then
	'BACKUP LOG'
    when action = 236 then
	'CREATE RULE'
    when action = 26 then
	'REFERENCES / DRI'
    else
        'SOME OTHER RIGHT'
    end 'ACTION',
    case when protecttype = 204 then
        'GRANT_W_GRANT'
    when protecttype = 205 then
        'GRANT'
    when protecttype = 206 then
        'DENY'
    end 'AccessType', sysusers.CreateDate, sysusers.UpdateDate,
Case when isntgroup = 1 then
	'YES'
     when isntgroup = 0 then
        'NO'
    end 'WindowsGroup',
Case when isntuser = 1 then
	'YES'
     when isntuser = 0 then
	'NO'
    end 'WindowsUser',
Case when issqluser = 1 then
	'YES'
     when issqluser = 0 then
	'NO'
    end 'SQLUser',
Case when issqlrole = 1 then
	'YES'
     when issqlrole = 0 then
	'NO'
    end 'SQLRole',
Case when isapprole = 1 then
	'YES'
     when isapprole = 0 then
	'NO'
    end 'AppRole'
from sysprotects
join sysobjects on sysprotects.id = sysobjects.id
join sysusers on sysprotects.uid = sysusers.uid
left outer join (Select sysusers.UID, sysusers.Name, 
		 sysmembers.MemberUid  
		 from sysusers
		 join sysmembers 
	         on sysusers.uid = sysmembers.groupuid
 	         where sysusers.issqlrole = 1) B
on sysusers.Uid = b.memberUid)
-- Apacs database users & permissions

FETCH NEXT from DBCur into @DBName

END

Close DBCur
Deallocate DBCur

Select * from #DBPermits
Order by DatabaseName, LoginName

Drop table #DBPermits

Any thoughts or ideas on what I'm doing wrong?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Jamfool]

From the thread: thread183-1153591

No Cool Handle Provided:

Create Proc ListAllNTUsersAndDBs
as
Create Table #DBUsers(DB varchar(3000),ssid varbinary(85))
Create Table #NTUsers(Accountname varchar(300),type varchar(300),Privilege varchar(300), mappedlogin varchar(300),permission varchar(300))
declare @dbname varchar(3000)
Select @dbname =''
while not @dbname is null 
    begin
      select @dbname = min(name) from master..sysdatabases where name > @dbname
    if  @dbname is null
        begin
            break
        end
        Insert Into #DbUsers (db,ssid)
        select @dbname, sid  from sysusers where isntgroup=1        
    end
--Select distinct sl.name,db.db from master..sysxlogins sl join #DBusers db on sl.sid = db.ssid
Declare @NtGroup varchar(300)
Select @ntGroup=''
while not @NtGroup is null
    begin
        Select @NtGroup = min(sl.name) 
        from master..sysxlogins sl join #DBusers db on sl.sid = db.ssid
         where sl.name > @NtGroup
        if @NtGroup is null
            begin
                break
            end
        insert into #NTUsers (AccountName,Type,Privilege,Mappedlogin,permission)
        EXEC xp_logininfo @NtGroup,'members'
end
select distinct accountname,name,db  
from #NTUSers N 
join (select sl.name,db.db from master..sysxlogins sl join #DBusers db on sl.sid = db.ssid) X 
on X.Name = N.Permission
drop table #NTUsers
drop table #DBUsers
go

and coupled with has_dbaccess function i managed to solve my problem.

 

[Catadmin]

Jamfool,

Thanks for the code. I'll see if I can't alter it to suit my needs. I actually need all users, NT & SQL only, not just NT Users.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Hmm. Running into the problem that if I have a SQL account or a Windows account with database access that is not (so far as I can tell) mapped to a Server login, then I can't pull up that person's info.

In Enterprise Manager, if I look at MyServer -> Databases -> MyDB -> Users, I can see a few logins that I don't see when I look under Server -> Security -> Logins.

Because of that, if I do a left outer join from sysusers to syslogins, I can see those users, but if I join from syslogins to sysusers, I can't see those users.

Anyone have thoughts on how to get the information on these types of users? If so, is it a "get the info on them as a seperate report" kind of thing, or is there a way to include this in my already existing code and get both birds with one grab?

Thanks,



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"