I'm using `sp_msforeachdb` to list all databases on my server.  

I know [it is unsupported](http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c45a7f66-1bef-4b96-87cc-62e556386478), but I wonder why it does not list all the databases I have installed.

This is how I run it:

```
set run="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S .\SQL2008 -E -h -1 -Q  
%run% "exec sp_msforeachdb 'select ''?'''"

```

Output: 

```
master 
tempdb 
model 
msdb 
balance166

```

Databases that SSMS shows:

```
master 
tempdb 
model 
msdb 
balance166
BOON205
KAB205

```

Both `BOON0205` and `KAB205` have been restored with commands like this:  

`%run% "..."` where `"..."` is a statement like this on one line: 

```
RESTORE DATABASE [BOON205] FROM DISK = N'C:\Data\Backup\Balance205.bak'
  WITH REPLACE,
  STATS = 10,
  MOVE N'Balance166' to N'C:\Data\Balance205.mdf', 
  MOVE N'Balance166_log' to N'C:\Data\Balance205_log.ldf';

```

The restore obviously works: I can connect to these databases fine.  

But `sp_msforeachdb` does not list them.

Why?  

Where could I start to find out?

**Edit**: Environment

* SQL Server 2008 version 10.0.1600.22 RTM Standard Edition
* Windows Server 2008 Standard
* logged on user is in a domain, but local admin on this machine, and added as Windows user to the SQL Server database security with a "GRANT CONTROL SERVER"

`sys.databases` is OK:

```
C:\bin>%sqlrun% -Q "select name from sys.databases"
name
-----------------------------------------------------------------------------
master
tempdb
model
msdb
balance166
Balance205
KAB205
BOON205
(8 rows affected)

```

`has_dbaccess` is OK, but `status` is probably not.
Need to check that out:

```
C:\bin>%sqlrun% -Q "select cast(name as varchar(10)), status, cast(status as varbinary(8)), DATABASEPROPERTY(name, 'issingleuser') as issingleuser, has_dbaccess(name) as has_dbaccess from master.dbo.sysdatabases"
           status                 issingleuser has_dbaccess
---------- ----------- ---------- ------------ ------------
master           65544 0x00010008            0            1
tempdb           65544 0x00010008            0            1
model            65536 0x00010000            0            1
msdb             65544 0x00010008            0            1
balance166       65536 0x00010000            0            1
Balance205  1073807361 0x40010001            0            1
KAB205      1073807361 0x40010001            0            1
BOON205     1073807361 0x40010001            0            1

```

It is not status, as `DATABASEPROPERTYEX('master', 'Status')` returns `ONLINE` for all of them.  

With the list of status codes found [here](http://www.mssqltips.com/tip.asp?tip=1477) and the [definition of sp\_msforeachdb](http://www.sqlhacks.com/FAQs/Stored-Procedures-Text), I disected it as this, which rules out `status` as an issue:

```
         8 0x00000008 - 'trunc. log on chkpt'
     65536 0x00010000 - 'online'
     65544 0x00010008 - 65536 + 8
1073741824 0x40000000 - 'invalid login'
1073807361 0x40010001 - 1073741824 + 65536 + 8

```

So the last 4 databases from the list have 'invalid login'.  

Time to look into security and rights...

--jeroen