can't see DB on SQL 2005 with SQL management studio

I am having a strange issue with SQL Management studio express. I can
log via the front end, but I do not see the DB for which this user is
"owner"
This is not the odd part. The odd part is that if I log in through a
3rd party front end such as EMS sql manager lite I do see the database
I own, and can manipulate it as I please. Can anyone suggest a reason
for this. Here are details of the setup just in case. The DB server
only has one DB other then the default system DB's. The owner of the db
I am having issue with has full permissions on the DB, but is not a
member of ANY server roles. There is a firewall on the machine but it
is allowing tcp connections on 1433. I have not set any connection
restrictions. The only major security change done on the server was to
use:
use master
Revoke View any database from public
go
AND
use *db I am working with*
Exec Sp_changedbowner ' *user that will own the db I am working with* 'Hi
Have you logged out/in since changing the owner of the database? Have you
tried to create a new database as that login, and can you see it? Have you
applied SP1?
John
"pr0f1t" wrote:
> I am having a strange issue with SQL Management studio express. I can
> log via the front end, but I do not see the DB for which this user is
> "owner"
> This is not the odd part. The odd part is that if I log in through a
> 3rd party front end such as EMS sql manager lite I do see the database
> I own, and can manipulate it as I please. Can anyone suggest a reason
> for this. Here are details of the setup just in case. The DB server
> only has one DB other then the default system DB's. The owner of the db
> I am having issue with has full permissions on the DB, but is not a
> member of ANY server roles. There is a firewall on the machine but it
> is allowing tcp connections on 1433. I have not set any connection
> restrictions. The only major security change done on the server was to
> use:
> use master
> Revoke View any database from public
> go
> AND
> use *db I am working with*
> Exec Sp_changedbowner ' *user that will own the db I am working with* '
>|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts
John Bell wrote:
> Hi
> Have you logged out/in since changing the owner of the database? Have you
> tried to create a new database as that login, and can you see it? Have you
> applied SP1?
> John
> "pr0f1t" wrote:
> > I am having a strange issue with SQL Management studio express. I can
> > log via the front end, but I do not see the DB for which this user is
> > "owner"
> > This is not the odd part. The odd part is that if I log in through a
> > 3rd party front end such as EMS sql manager lite I do see the database
> > I own, and can manipulate it as I please. Can anyone suggest a reason
> > for this. Here are details of the setup just in case. The DB server
> > only has one DB other then the default system DB's. The owner of the db
> > I am having issue with has full permissions on the DB, but is not a
> > member of ANY server roles. There is a firewall on the machine but it
> > is allowing tcp connections on 1433. I have not set any connection
> > restrictions. The only major security change done on the server was to
> > use:
> >
> > use master
> > Revoke View any database from public
> > go
> >
> > AND
> >
> > use *db I am working with*
> > Exec Sp_changedbowner ' *user that will own the db I am working with* '
> >
> >|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts|||Hi
That sounds like the change of ownership has not been reflected in the other
database. For the new database did you create the database as the login or
change the database to be owned by the login?
If you log in as a sysadm you should be able to see that database?
Try changing the owner again to a different login and then change it back.
If you run SELECT * FROM master.sys.databases what does it return?
Try regranting the VIEW ALL DATABASES to public and then just deny it to
specific logins.
What version of SQL 2005 are you using?
John
"pr0f1t" wrote:
> that is a fantastic suggestion. I don't knwo why I did not try this
> first. Yes the new DB is visible if I make this user owner. So why is
> the origional DB not visible if this user is owner of that as well? Any
> thoughts
>
> John Bell wrote:
> > Hi
> >
> > Have you logged out/in since changing the owner of the database? Have you
> > tried to create a new database as that login, and can you see it? Have you
> > applied SP1?
> >
> > John
> >
> > "pr0f1t" wrote:
> >
> > > I am having a strange issue with SQL Management studio express. I can
> > > log via the front end, but I do not see the DB for which this user is
> > > "owner"
> > > This is not the odd part. The odd part is that if I log in through a
> > > 3rd party front end such as EMS sql manager lite I do see the database
> > > I own, and can manipulate it as I please. Can anyone suggest a reason
> > > for this. Here are details of the setup just in case. The DB server
> > > only has one DB other then the default system DB's. The owner of the db
> > > I am having issue with has full permissions on the DB, but is not a
> > > member of ANY server roles. There is a firewall on the machine but it
> > > is allowing tcp connections on 1433. I have not set any connection
> > > restrictions. The only major security change done on the server was to
> > > use:
> > >
> > > use master
> > > Revoke View any database from public
> > > go
> > >
> > > AND
> > >
> > > use *db I am working with*
> > > Exec Sp_changedbowner ' *user that will own the db I am working with* '
> > >
> > >
>|||*dbname* 5 NULL 0xC1F615C4866765479D8B0AD5FD9FDEAA 2006-10-31
14:53:43.060 80 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 1 TORN_PAGE_DETECTION 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 9063F0BE-B2B1-4A45-9036-8775AAB1B47A 0 0 NOTHING 0
is the result of SELECT * FROM master.sys.databases
I am running SQL Server 9.0.2047. I tried to change the ownership of
the DB but it only results in errors pretaining to object and schema
ownershipo, but I cannot seem to change the ownership to another user.
Ifs there a way to do this with TSQL? I have one user that essentially
needs to hve ownership of the DB, and access via a front end
porefereably Microsoft SQL managment studio express.
John Bell wrote:|||Hi
Can you post the exact error number and message that occurs when you try to
change the owner? I assume you are using sp_changedbowner to do this?
Try creating a new user and changing the schema ownership to them
John
"pr0f1t" wrote:
> *dbname* 5 NULL 0xC1F615C4866765479D8B0AD5FD9FDEAA 2006-10-31
> 14:53:43.060 80 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 1 TORN_PAGE_DETECTION 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 9063F0BE-B2B1-4A45-9036-8775AAB1B47A 0 0 NOTHING 0
>
> is the result of SELECT * FROM master.sys.databases
> I am running SQL Server 9.0.2047. I tried to change the ownership of
> the DB but it only results in errors pretaining to object and schema
> ownershipo, but I cannot seem to change the ownership to another user.
> Ifs there a way to do this with TSQL? I have one user that essentially
> needs to hve ownership of the DB, and access via a front end
> porefereably Microsoft SQL managment studio express.
>
> John Bell wrote:
>