vrijdag 28 november 2008

sp_ListNTUserNameDatabaseAccess

Not Compelete (To DO -- Next Week):
===================================
http://lynchtek.com/sp_ListNTUserNameDatabaseAccess.aspx
----------------------------------------------------------
ALTER Proc usp_ListAllNTUsersAndDBs (@NtGroup varchar(300))
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 master..sysusers Where isntgroup=1

end

Select @ntGroup=''

While Not @NtGroup is Null
begin

Select min(sl.name)
From master..sysusers 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..sysusers sl join #DBusers db on sl.sid = db.ssid) X
on X.Name = N.Permission

drop Table #NTUsers
drop Table #DBUsers

exec usp_ListAllNTUsersAndDBs 'BUILTIN\Administrators'

Geen opmerkingen:

Een reactie posten