Solved

List All DBs To Which A User Has Access

Posted on 2003-10-30
13
1,208 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlDataBase 7 48
Abstract Express Replacement Software 12 25
Sql server function help 15 30
Help creating a spatial object in SQL Server 4 22
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

770 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