Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

List All DBs To Which A User Has Access

Posted on 2003-10-30
13
Medium Priority
?
1,257 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 15

Assisted Solution

by:namasi_navaretnam
namasi_navaretnam earned 200 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 200 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

704 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