MS SQL Server - Object Browser in Query Analyzer

Author

Object Browser in Query Analyzer

MANCPOLYMAN

2006-01-27, 8:23 pm

In Test environemnt user has access to DB1, they log in and in the drop down
box of DB names is DB1 but it does not appear in the object browser.
In Dev environemnt I do the same thing and the user can see DB1 in object
Browser.

This is driving me nuts, any help or advice appreciated

Thanks

Gert E.R. Drapers

2006-01-28, 11:23 am

ObjectBrowser uses sp_MShasdbaccess, so your security settings are not
identical between your two environments, maybe you are a sysadmin in one
case?

This is the proc so you can see the underlying query.
-- ====================
====================
=====
-- sp_MShasdbaccess
-- ====================
====================
=====
-- List all databases a user has access to
-- along with their db properties
--
-- PARAMETERS: N/A
--
-- REMARKS: for SQL Server 7.0 and 8.0
-- ====================
====================
=====
create proc sp_MShasdbaccess
as

set nocount on
set deadlock_priority low

select name as 'dbname',
owner = substring(suser_snam
e(sid), 1, 24),
DATABASEPROPERTY(nam
e, N'IsDboOnly') as 'DboOnly',
DATABASEPROPERTY(nam
e, N'IsReadOnly') as 'ReadOnly',
DATABASEPROPERTY(nam
e, N'IsSingleUser') as 'SingleUser',
DATABASEPROPERTY(nam
e, N'IsDetached') as 'Detached',
DATABASEPROPERTY(nam
e, N'IsSuspect') as 'Suspect',
DATABASEPROPERTY(nam
e, N'IsOffline') as 'Offline',
DATABASEPROPERTY(nam
e, N'IsInLoad') as 'InLoad',
DATABASEPROPERTY(nam
e, N'IsEmergencyMode') as 'EmergencyMode',
DATABASEPROPERTY(nam
e, N'IsInStandBy') as 'StandBy',
DATABASEPROPERTY(nam
e, N'IsShutdown') as 'ShutDown',
DATABASEPROPERTY(nam
e, N'IsInRecovery') as 'InRecovery',
DATABASEPROPERTY(nam
e, N'IsNotRecovered') as 'NotRecovered'

from master.dbo.sysdatabases
where has_dbaccess(name) = 1
order by name
-- ====================
====================
=====
-- end sp_MShasdbaccess
-- ====================
====================
=====

GertD@SQLDev.Net

"MANCPOLYMAN" < MANCPOLYMAN@discussi
ons.microsoft.com> wrote in message
news:A35E3B8A-6A95-41D9-A42E- C31EB37A9063@microso
ft.com...
> In Test environemnt user has access to DB1, they log in and in the drop
> down
> box of DB names is DB1 but it does not appear in the object browser.
> In Dev environemnt I do the same thing and the user can see DB1 in object
> Browser.
>
> This is driving me nuts, any help or advice appreciated
>
> Thanks

MANCPOLYMAN

2006-01-30, 8:23 pm

Gert,

Thanks a lot Gert, that helped. I saw the owner was null v sa I changed
the null to sa and restored that one but still the same. Do i need to
reboot, stop/start, etc?

Again, many thanks.
MPM

"Gert E.R. Drapers" wrote:

> ObjectBrowser uses sp_MShasdbaccess, so your security settings are not
> identical between your two environments, maybe you are a sysadmin in one
> case?
>
> This is the proc so you can see the underlying query.
> -- ====================
====================
=====
> -- sp_MShasdbaccess
> -- ====================
====================
=====
> -- List all databases a user has access to
> -- along with their db properties
> --
> -- PARAMETERS: N/A
> --
> -- REMARKS: for SQL Server 7.0 and 8.0
> -- ====================
====================
=====
> create proc sp_MShasdbaccess
> as
>
> set nocount on
> set deadlock_priority low
>
> select name as 'dbname',
> owner = substring(suser_snam
e(sid), 1, 24),
> DATABASEPROPERTY(nam
e, N'IsDboOnly') as 'DboOnly',
> DATABASEPROPERTY(nam
e, N'IsReadOnly') as 'ReadOnly',
> DATABASEPROPERTY(nam
e, N'IsSingleUser') as 'SingleUser',
> DATABASEPROPERTY(nam
e, N'IsDetached') as 'Detached',
> DATABASEPROPERTY(nam
e, N'IsSuspect') as 'Suspect',
> DATABASEPROPERTY(nam
e, N'IsOffline') as 'Offline',
> DATABASEPROPERTY(nam
e, N'IsInLoad') as 'InLoad',
> DATABASEPROPERTY(nam
e, N'IsEmergencyMode') as 'EmergencyMode',
> DATABASEPROPERTY(nam
e, N'IsInStandBy') as 'StandBy',
> DATABASEPROPERTY(nam
e, N'IsShutdown') as 'ShutDown',
> DATABASEPROPERTY(nam
e, N'IsInRecovery') as 'InRecovery',
> DATABASEPROPERTY(nam
e, N'IsNotRecovered') as 'NotRecovered'
>
> from master.dbo.sysdatabases
> where has_dbaccess(name) = 1
> order by name
> -- ====================
====================
=====
> -- end sp_MShasdbaccess
> -- ====================
====================
=====
>
> GertD@SQLDev.Net
>
> "MANCPOLYMAN" < MANCPOLYMAN@discussi
ons.microsoft.com> wrote in message
> news:A35E3B8A-6A95-41D9-A42E- C31EB37A9063@microso
ft.com...
>
>
>

Sponsored Links