May 28, 2008 at 5:19 am
I log on to the server running SQL Server 2005 using a local administrator account.
When I run the Management Studio and log in with Windows authentication, only the system databases show in Object Explorer.
In addition, many other objects in other categories are missing.
I have not disabled the built-in administrators group in SQL Server.
May 28, 2008 at 8:30 pm
Is the BUILTIN\Administrators group still a member of the sysadmin fixed server role?
K. Brian Kelley
@kbriankelley
May 29, 2008 at 5:11 am
Yes, it is.
An additional detail which may or may not be relevant:
The account with which I log on to the server is a member of the AD Domain Administrators group. Both the account itself and the Domain Administrators group are in the local Administrators group on the server.
--->I know that this is a bad security practice but I will address this after I solve the problem at hand.
May 29, 2008 at 8:37 am
If you log in with a different account you see the rest of the databases, etc.? Hrm, I've not seen that one before. What happens if you open up a query window. Can you access a database that, for whatever reason, is not showing in the GUI?
K. Brian Kelley
@kbriankelley
May 29, 2008 at 8:51 am
Yes, other accounts with less capability (but in the local Administrators group) can see the databases with no problem.
My account cannot create a query because the user databases do not appear in the drop down list.
May 30, 2008 at 8:30 am
Has BUILTIN\Administrators been denied access to anything specifically?
I presume you have another admin level account (like sa or the DAC) that you can use to check all of this out...if you can't find any specific deny or missing permissions that explain it you could always use one of those logins as a last resort to drop and recreate BUILTIN\Administrators.
Kendal Van Dyke
June 2, 2008 at 7:10 am
Here is something really odd:
If I log into the server itself with my account (a local administrator) and log into Management studio using the SQl Server 'sa' user, I still get the same behavior (can't see user databases).
It is almost like my account is 'defeating' the database user.
June 2, 2008 at 12:37 pm
Members of the sysadmin fixed server role should be bypassing security checks, but check to see if there has been any denies leveled against the sysadmin fixed server role.
K. Brian Kelley
@kbriankelley
June 2, 2008 at 12:53 pm
How do I tell if anything has been denied to the role?
June 2, 2008 at 2:09 pm
You can run
EXEC sp_helprotect @username = 'sysadmin';
It won't pick up SQL Server 2005 securables (server, schema), but it will pick up denies against explicit tables, etc.
K. Brian Kelley
@kbriankelley
June 2, 2008 at 2:56 pm
Is it possible that Management Studio for your Windows login is defaulting to a different instance? Run: select @@servername while logged in as users who can see the databases and those who cannot. If there are different results, that may be your problem.
It may not be the problem, but it's worth a look if nothing else but to rule it out.
You might also try select * from sys.databases when logged in as SA just to verify it is not management studio issue.
Kyle
June 3, 2008 at 10:12 am
I tried all of the suggested solutions and was still unable to see my complete configuration in Management Studio.
I uninstalled and reinstalled SQL server and was unable to log into Management Studio at all using my account.
I then deleted my Windows 2003 Server user profile, logged off and logged back on which recreated my user profile.
Everything in now looks correct in Management Studio.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply