Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
supersql
Yak Posting Veteran
99 Posts |
 Posted - 2011-04-26 : 09:50:55
|
| This sounds weired to me, I have a service account on my SQL server 2008 which is a sysadmin and also admin on the box too but when i query "select name from master.sys.databases" only master and tempdb are listed all other databases are not seen,this happens only on SQL 2008 servers.But when i run it as my id (also sysadmin) i get list of all database, strange behaviour, need some help please. |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-04-26 : 10:21:48
|
| USE MASTERGOGRANT VIEW ANY DATABASE TO [TargetUserNameHere];-------------------------http://connectsql.blogspot.com/ |
 |
|
|
supersql
Yak Posting Veteran
99 Posts |
Posted - 2011-05-02 : 13:55:54
|
| update...Here is what i found. I tried starting maangement studio using the service account which is the one that has problem and it could pretty much do every thing as sys admin and also returned list of all databases using "select name from master.sys.databases" but the problem is narrowed down to sql agent job now.I use this query to backup all the databases using a sql agent job and it does backup only master database as the query returns only 2 names (master and tempdb). So what would be the problem with my job in slq 2008 which would not list all the db names? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-05-02 : 14:10:55
|
| See lionofdezert's post aboveUSE MASTERGOGRANT VIEW ANY DATABASE TO [SQL Agent Service Account Here];Also you'll want to make this account a member of db_backupoperator for each db, or make it sysadmin. |
|
|
|
|
|
|
can not list db names as sysadmin