March 28, 2011 at 7:53 am
I could not list all database names using this query in sql 2008, it gives me only master and temdb database, any reason?
select name from master.sys.databases
March 28, 2011 at 8:26 am
Are you certain you're running it on the correct server? As I'm sure you're already aware, this returns all DB's that are "online", no matter where you run it:
SELECT name FROM sys.databases:
master
tempdb
model
msdb
ReportServer
ReportServerTempDB
MyDB1
MyDB2
MyDB3
MyDB4
...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 28, 2011 at 8:34 am
yes, ia m on the right server and i am sysadmin on that machine, this is killing me.
March 28, 2011 at 8:50 am
Only thing that sticks out to me is security. I'm assuming you use mixed mode authentication and you're logged into the SQL Server using your windows account...my only suggestion is to log in directly as SA (or another SQL Server test account that has the same rights) and run it under that user to see if you get different results.
SQL Server security can be quirky. Even though your account is sysadmin, check that you have sysadmin privilleges on each of the databases you can't see.
Wish I could help more...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 28, 2011 at 10:02 am
But i i am doing the same on SQL 2005 from the past 3 years without any issues, why only in SQL 2008
March 28, 2011 at 10:10 am
Have you verified that you are sysadmin at the time you run this? What does this give you?
SELECT IS_SRVROLEMEMBER('sysadmin');
Or this?
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
March 28, 2011 at 12:29 pm
it gives "1" I am an admin for sure because i do all kinds of admin stuff . any further help?
April 26, 2011 at 7:48 am
I still could not solve this problem, this is happening only on SQL 2008 servers, any reason?
SELECT IS_SRVROLEMEMBER('sysadmin');--this query gives nothing to me
SELECT * FROM fn_my_permissions(NULL, 'SERVER');--this query returns 0
I am sure i gave sysadmin previalges, any troubleshooting steps?
April 26, 2011 at 8:24 am
Most likelly a privileges issue.
Log as "sa" and re-run query, if it returns what you expect the concentrate on assigned and missing privileges.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 26, 2011 at 8:36 am
You've got to be assigning privileges to the wrong account or they are logging in with the wrong account, or you have some strange DENYs issued on some tables.
I would start over with a new account, assign sysadmin and see what happens.
May 2, 2011 at 10:41 am
I checked everything mentined above but it is still not working for me. Its not just one server but it is reacting the same for 3 2008 servers, very weired, please help.
May 2, 2011 at 11:09 am
I am use my own id asa a sysadmin i can do this without any problems but this problem is only for service account id which is also a sysadmin and admin on the machine, does this help to troubleshoot more?
May 2, 2011 at 11:55 am
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?
May 2, 2011 at 8:20 pm
Is SQL Server Agent running under the same service account? Was that account added separately to SQL Server and granted sysadmin, or are you relying on group membership?
Do you have either of the following accounts as logins in SQL Server? NT AUTHORITY\MSSQLSERVER or NT AUTHORITY\SQLSERVERAGENT
And finally, what account is the owner of the jobs? If this is the service account - try changing the owner to sa and see if that corrects the problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 10, 2011 at 12:32 pm
yes, SQl Server, Agent and all other sql related services use the same windows account which is a sysadmin.
Looks like the problem is only when i run the job but i can have no problem when connect to sql server by my id or service account.
Also i tried changing the owner of the job to "sa" it still finds only master and tempdb database. Actually this takes backup of all dataabases in the server but it is doing only master, its really killing me.
Why does this happen only to SQL 2008 servers but not 2005, the reason i ask is 3 of my servers which are in 2008 R2 behaves same.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply