Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

TSQL - How do I use Cursor to query other dbs and return a new RS?

Status
Not open for further replies.

redmurphy

Programmer
May 1, 2006
6
US
Hi,

I have need to query the master database to get a list of all databases, and then use a passed-in username to query every database's sysusers table to see if a specific user has permission to access the database. Then, I need to return a recordset to an asp page that contains only the list of databases that the user has access to.

I've been reading tsql tutorials and I think there must be a way to user a Cursor to do this, but can't seem to figure out how.. Any help is greatly appreciated.

Thanks,

Mike
 
Consider avoiding the cursor by using the undocumented stored procedure "sp_MSforeachdb" that will loop through all databases for you. I've pasted an example below that was copied from elsewhere simply as an example to get you started. You'll obviously want to use a different command.

EXEC sp_MSforeachdb @command1="print '?' DBCC CHECKDB ('?')"

Hope it helps,
Dalton
 
Thank you! I've been looking all over for something like that.

Please bear with me, I'm brand new to sql and I'm still confused about how to get what I want here. in the command portion, I obviously need to supply a qry to be run on each table. But the thing is, in each qry I need to return the name of the database if any records are found for the username that I'm passing in. Can you help with this?

Thanks again,

Mike
 
I'm not sure what you mean by each table. Are you looking to find out which users "OWN" tables in the databse, or users that have some permissions to the database?
 
If I run this:
<code>
EXEC sp_MSforeachdb @command1="select * from sysusers"
</code>

It seems to run the query on the same database every time. It gives me a 18 matching result sets (there are 18 dbs on the server)
 
Sorry. I keep accidentally saying "table" when I mean "database".

What I need to do is query the sysusers table, which has a list of all users with permission to access the database, in every database. Then, I need to return the list of databases that a specific user has permission to use.

Does that make any more sense? I have a feeling that I may be going at this backwards and making this harder to understand.
 
Thank you so much for that! I'm all set. Thanks again.
 
I had a little fun learning on this and what I found was that I could select the value of the ? if I wrapped it in quotes. In the above the DB_NAME() function is used to get the name, in the below I wrap the ? in quotes and get the database name as well. You still need all of the above
Code:
EXEC sp_MSforeachdb @command1='use ? select ''?'',name from sysusers'
You can also get rid of the USE if you'd like to by using the following command:
Code:
EXEC sp_MSforeachdb @command1='select ''?'',name from ?..sysusers'
Or you can get rid of most of the above and just pull the databases for a particular user by using the following:
Code:
EXEC sp_MSforeachdb @command1='select ''?'' from ?..sysusers where name = ''user_name'''
Hope you have more choices now than you ever dreamed of.


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top