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

[redmurphy]

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

 

[druer]

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

 

[redmurphy]

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

 

[druer]

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?

 

[redmurphy]

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)

 

[SQLDenis]

EXEC sp_MSforeachdb @command1="use ? select * from sysusers"

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[redmurphy]

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.

 

[SQLDenis]

create table #TestUser (DBName varchar(99),SysUserName varchar(99))

insert into #TestUser
EXEC sp_MSforeachdb @command1="use ? select db_name(),name from sysusers"

select * from #TestUser
where SysUserName ='public'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[redmurphy]

Thank you so much for that! I'm all set. Thanks again.

 

[druer]

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

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:

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:

EXEC sp_MSforeachdb @command1='select ''?'' from ?..sysusers where name = ''user_name'''

Hope you have more choices now than you ever dreamed of.

 

[redmurphy]

Thanks!