Security check 2

[Jamfool]

Hi all.

Please can someone provide me with the sql which checks if a user has access to read the object createdate from the
sysobjects table in all the databases on the server. I presume they just need to have read access or greater on the database.

 

[NoCoolHandle]

To have read access to any system table in any database all a "login" needs is to have access to that database. This usually requires either a "guest" account or a "user" account in the database...

HTH

Rob

PS no permissions are needed as the "Public" role has Read on the system tables.

 

[Jamfool]

Bascically how do you run a script on all databases a user has access to?

(Script below will return nt groups in its result..not the user who is logged in..so iam not sure if there in the group)

So as below.

select DISTINCT o.name, loginname = (case when (o.sid = 0x00) 
then NULL else l.loginname end), user_name(o.gid), o.uid, o.hasdbaccess 
from dbo.sysusers o left join 
(select sid, loginname, 1 as matched from master.dbo.syslogins) l 
on l.sid = o.sid where ((o.issqlrole != 1 and o.isapprole != 1 and o.status != 0 
and matched is not null) or (o.sid = 0x00) and o.hasdbaccess = 1) 
and o.isaliased != 1

 

[NoCoolHandle]

So... if I am reading your question correctly..

You are only concerned with NT (windows) Users..
Your Logins are NT Groups
You want to know what the effective Database available to any given "NT USer". Being aware that a single "NT USer" can be in One or more "NT GROUPS".


I.e. IF you run Select Suser_Sname() you get

domain\UserName

You then need to know what
What NT Groups domain\username is in... and therefore what databases does Domain\username have access to.

Is that correct?

 

[Jamfool]

yes thats it.

so user

mydomain\jam

present in group nt sql_access_lvl1
but not nt group sql_access_lvl2

 

[NoCoolHandle]

I will see if I can find any old code that delt with that..

The clue that might help you is [purple]xp_enumgroups[/purple] or something like that.. It should let you enumerate through all the NT groups Users... At that point you link the Name col from sysxlogins whith the NT Group.. Create a table of Group Members, do that to each NT Login and at that point it is a sql query..

With occasional updates..

HTH

Rob

 

[NoCoolHandle]

Also take a look at EXEC

[green]xp_logininfo 'mydomain\jam'[/green]

This might be the answer you need.

 

[NoCoolHandle]

See if this works...

Use Master
go
Create Proc ListAllNtUsersAndDatabases
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 lanemessages..sysusers where isntgroup=1		
	end
join #DBusers db on sl.sid = db.ssid
insert into #NTUsers (AccountName,Type,Privilege,Mappedlogin,permission)
EXEC xp_logininfo 'traffic\toll collectors','members'
join #DBusers db on sl.sid = db.ssid
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

 

[NoCoolHandle]

Sorry need to check stuff before I post it....
don't run that script yet..


Fotgot to add the loop to go throught the logins...

Has a hard coded value. (you won't have that
group)

 

[NoCoolHandle]

OK... This should work

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 lanemessages..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

 

[Jamfool]

Thanks for taking the time to look at this one NoCoolHandle.

 

[NoCoolHandle]

Jam..

There were still some bugs in the last ver..
I did some work over the weekend (this one was bothering me, i spotted another issue Saturday.. I now remember why it was never finished... but..... The new code is about 99% to 100% now.. (send feedback)

Try this

Alter Proc sp_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))
Create Table #NTUsers2(Accountname varchar(300),type varchar(300),Privilege varchar(300), mappedlogin varchar(300),permission varchar(300))
Create Table #Admins([Name] varchar(3000),DB varchar(3000))
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)
        exec ('select ''' + @dbname + ''', sid  from ' + @dbname + '..sysusers where isntname=1')
  end

Declare @NtGroup varchar(300)
Declare @Type varchar(300)

Select @ntGroup=''
while not @NtGroup is null
    begin
        Select @NtGroup = min(sl.name) 
        from master..sysxlogins sl left Outer join #DBusers db on sl.sid = db.ssid 
        where sl.name > @NtGroup and sl.name like '%\%'
        if @NtGroup is null
            begin
                break
            end
        insert into #NTUsers2 (AccountName,Type,Privilege,Mappedlogin,permission)
        EXEC master..xp_logininfo @NtGroup        
        If exists (select * from #NtUsers2 where type='group') 
            begin
                if not @NtGroup ='nt authority\system' 
                begin
                    insert into #NTUsers (AccountName,Type,Privilege,Mappedlogin,permission)
                    EXEC master..xp_logininfo @NtGroup,'members'
                end
            end
        else
            Begin
            insert into #NTUsers (AccountName,Type,Privilege,Mappedlogin,permission)
                EXEC master..xp_logininfo @NtGroup        
            end
    
    if Exists (Select * from #NTUsers2 where Privilege ='admin')
        begin
            insert into #Admins 
            Select MappedLogin ,db.Name from #NTUsers2 cross join SysDatabases db
        end
    delete from #NtUsers2
end
-- Get Special BuiltIn Group Membership
select distinct accountname as [NtUserLogin],x.name as [SQLLogin],db as [Access to Database]
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.mappedlogin
Union
Select a.[name] as [Windows Account],b.Permission [Windows Group],a.[db] [DataBase] from 
#Admins a join #NTUsers b 
on a.Name = b.AccountName  and not Permission is null

drop table #NTUsers
drop table #DBUsers

 

[Jamfool]

setuser 'group\user'

select * from Database_name.dbo.some_tbl
Server: Msg 916, Level 14, State 1, Line 1
Server user 'group\user' is not a valid user in database 'Database_name'.

The main problem iam getting is trying to execute some sql on databases that the user doesnt have access to and being unable to trap/avoid this.

 

[NoCoolHandle]

Jam..
Is this an issue with code from an app or QA?

It sounds like you are trying to make certain a specific user has permission to access a specific database to perform some action...

If it is an applicaion, is it the connection that gets this, or does it only happen when they try to access the database?

Are you working with both NT Groups and NT Accounts?

You could use that stored proc to build a table in master that you query to see if the Current login has permsissions.

Otherwise you will probably find that you just need to wait for that error.. (trappable in the app.. - that same error also shows up if the database doesn't exist

Another option (if the app can be modified) would be to look into "applicaion roles" - do a search in BOoks On Line

HTH

Rob

 

[Jamfool]

Managed to solve this using has_dbaccess function

 

[NoCoolHandle]

A star to you...