Solved

List All DBs To Which A User Has Access

Posted on 2003-10-30
13
1,234 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Agent Job Error 13 82
T-SQL Query 9 37
Sql server query 8 21
SQL query joining 6 tables in asp.net 4 32
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

752 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