Solved

List All DBs To Which A User Has Access

Posted on 2003-10-30
13
1,245 Views
Last Modified: 2008-03-06
I know I can run sp_helplogins and get all the databases for which a user has access but we're building a custom security application that will need to add and drop user's logins.

Before I go and tear sp_helplogins apart and rebuild it to only return the second set of records I wanted to see if anyone else had already cracked this nut.

Basicall I need a SQL query that will return all the databases to which a given login name has access. I could do this as a stored procedure call if someone has that.

Until I hear back, I'll keep looking at writing my own version of "sp_helplogins lite."

David
0
Comment
Question by:David Little
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9654017
Hi dplittle,

sp_helplogins [ [ @LoginNamePattern = ] 'login' ]


Cheers!
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9654030
sorry didn't read your post
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9654839
Here is the basic idea,

Modify as necessary. Eventhough you said login, I thing you actually meant user on each database. If logins as Lowfatspread suggests sp_helplogins will help.

Drop Procedure sp_GetLogonInfo
go
Create Procedure sp_GetLogonInfo
(
  @vUser varchar(50) = 'sa'
)
AS
BEGIN
DECLARE @v_name varchar(30),
        @sqlstring varchar(1000)
DECLARE database_cursor CURSOR FOR
select distinct name from master.dbo.sysdatabases
DECLARE @vMsg varchar(1000)
OPEN database_cursor
FETCH NEXT FROM database_cursor
INTO @v_name
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQLString = 'SELECT distinct uid,  name, ''' + @v_name + ''' FROM  ' + @v_name + '.dbo.sysusers WHERE name = ''' + @vUser + ''''
    EXEC (@SQLString)
   
   
   FETCH NEXT FROM database_cursor
   INTO @v_name

END
CLOSE database_cursor
DEALLOCATE database_cursor
END


HTH

Namasi Navaretnam
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 15

Assisted Solution

by:namasi_navaretnam
namasi_navaretnam earned 50 total points
ID: 9654865
Here you go!! This is even better.

Drop Procedure sp_GetLogonInfo
go
Create Procedure sp_GetLogonInfo
(
  @vUser varchar(50) = 'sa'
)
AS
BEGIN
DECLARE @v_name varchar(30),
        @sqlstring varchar(1000)

CREATE TABLE #TEMPTABLE (
  uid int,
  name varchar(50),
  dbname varchar(50)
)

DECLARE database_cursor CURSOR FOR
select distinct name from master.dbo.sysdatabases
DECLARE @vMsg varchar(1000)

OPEN database_cursor
FETCH NEXT FROM database_cursor
INTO @v_name
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQLString = 'INSERT  #TEMPTABLE SELECT distinct uid,  name, ''' + @v_name + ''' FROM  ' + @v_name + '.dbo.sysusers WHERE name = ''' + @vUser + ''''
    EXEC (@SQLString)
   
   
   FETCH NEXT FROM database_cursor
   INTO @v_name

END
CLOSE database_cursor
DEALLOCATE database_cursor

select * from #TEMPTABLE
where uid is not null

END

go

exec sp_GetLogonInfo user_id

go
0
 
LVL 34

Expert Comment

by:arbert
ID: 9655635
Personally, I wouldn't use a cursor OR query the system table directly (future changes you know), but to each his own.....What are you building your "application" in?  SQLDMO has some nice options to do stuff....
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9656913
Brett,

How would you join sysdatabases to sysusers table? I am not sure how you would write a query to accomplish this? When you say something I am sure there is a point. Please elobrate.

Thanks,

Namasi.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9658650
dplittle,

Any Comments!!
0
 
LVL 34

Expert Comment

by:arbert
ID: 9658713
"I am not sure how you would write a query to accomplish this? "  That's why I asked dplittle exactly what he was doing--I'm not sure I would want to write a query to accomplish this--might break with the next hotfix or service pack....
0
 
LVL 2

Author Comment

by:David Little
ID: 9658849
The app is being built in VB so I'd be interested in SQLDMO guidance.

Also, the requirements have changed a little ...

All we need now is a TRUE/FALSE if a login has access to ANY database ...

I'm thinking a UDF would be the best approach but don't really want to stick a cursor in a UDF. Any ideas?

I thought just having the function call sp_helplogins and then check @@ROWCOUNT but it always returns '1' ... even if the login name doesn't exist.

David
0
 
LVL 34

Accepted Solution

by:
arbert earned 50 total points
ID: 9659355
Not totally positive, but you could try to do something like this:

connection.execute ("set nocount on")

adorecordset=connection.execute("sp_helplogins")

adorecordset.nextrecordset()       'this gets to the second recordset
                                                 


0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9668258
dplittle,

Have you figured out a SQLDMO way fo doing this? I learn from MS that SQLDMO uses system tables as well. It is unlikely that they change exiting column names on system tables (especially on sysdatabaes or sysusers table). But they may add new fields. I have been using system tables since version 4.0 and have not had ant trouble. Plus using cursor is not a huge overhead as it does not deal with many rows. (Only a few records from sysdatabases)


Let us know if you need further help.

Namasi.
0
 
LVL 34

Expert Comment

by:arbert
ID: 9668393
It's not the amount of rows that affect a cursor--it's the overhead of creating and opening a cursor....Also, system tables do change--they just changed with SP3 on certaing things.

If SQLDMO changes, I'm sure there will be new releases just like when MDAC changes--it is BAD practice to use system tables even if "I have been using system tables since version 4.0"--shame on your for even recommending it when there are other "built-in" system alternatives....

I said SQLDMO might be an option--I also said that sp_helplogins will work with an ado recordset.
0
 
LVL 2

Author Comment

by:David Little
ID: 9739540
Thanks  Namasi  and Brett. I doubld the points and split them between the two of you. Both useable answers.

David
 
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question