Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

List All DBs To Which A User Has Access

Posted on 2003-10-30
13
Medium Priority
?
1,264 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

824 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