"Brian Henry" <nospam@nospam.com> wrote in message
news:u149Tc$MGHA.3944@tk2msftngp13.phx.gbl...
> In sql server 2000 we used sp_databases for users to return a list of
> databases (the users were not admins and had no special permissions
> besides had rights to a database or two) but in 2005 the non admins when
> they execute sp_databases to get a list back, well get nothing back! what
> has changed? the only thing i can see is in 2000 they had permissions like
> this
>
> Permissions
> Execute permissions default to the public role.
>
> but in 2005 it says select permission must be on the schema... I REALLY
> need to return a database list to users who are not admins... what can I
> do? thanks!
>
>

"Brian Henry" <nospam@nospam.com> wrote in message
news:O6wh5x$MGHA.3960@TK2MSFTNGP09.phx.gbl...
> when i execute this on the machines that dont show databases with
> sp_databases i get back a list..
>
> select name from master.dbo.sysdatabases where has_dbaccess(name) = 1
>
>
>
> so why cant sp_databases show it?
>
> "Brian Henry" <nospam@nospam.com> wrote in message
> news:u149Tc$MGHA.3944@tk2msftngp13.phx.gbl...
>> In sql server 2000 we used sp_databases for users to return a list of
>> databases (the users were not admins and had no special permissions
>> besides had rights to a database or two) but in 2005 the non admins when
>> they execute sp_databases to get a list back, well get nothing back! what
>> has changed? the only thing i can see is in 2000 they had permissions
>> like this
>>
>> Permissions
>> Execute permissions default to the public role.
>>
>> but in 2005 it says select permission must be on the schema... I REALLY
>> need to return a database list to users who are not admins... what can I
>> do? thanks!
>>
>>
>
>

"Gail Erickson [MS]" <ga***@online.microsoft.com> wrote in message
news:OgEmWdCNGHA.420@tk2msftngp13.phx.gbl...
> Hi Brian,
>
> What's changed is that most of the metadata in SQL Server 2005 has limited
> visibility. The Books Online topic "Metadata Visibility Configuration"
> states the following:
>
> "In earlier versions of SQL Server, metadata for all objects in a database
> is visible to members of the public role. This means that any user that is
> logged on to an instance of SQL Server can view metadata for every object
> in the server, even those objects on which a user has no rights.  In SQL
> Server 2005, the visibility of metadata is limited to securables that a
> user either owns or on which the user has been granted some permission."
> It's worth reading the entire topic to understand why this change in how
> object metadata is exposed was made.
>
> sp_databases actually selects from sys.master_files and not sys.databases.
> Sys.master_files is not granted public visibility so the sproc isn't
> returning values for those users that don't have admin permissions.
>
> The fix is to grant  the VIEW ANY DEFINITION permission to the users that
> need to view all rows returned by sp_databases.  See the topic
> "Troubleshooting Metadata Visibility "  and "VIEW DEFINITION Permission "
> for additional details. The syntax would be GRANT VIEW ANY DEFINITION TO
> <user | role | login>.
>
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
>
> "Brian Henry" <nospam@nospam.com> wrote in message
> news:O6wh5x$MGHA.3960@TK2MSFTNGP09.phx.gbl...
>> when i execute this on the machines that dont show databases with
>> sp_databases i get back a list..
>>
>> select name from master.dbo.sysdatabases where has_dbaccess(name) = 1
>>
>>
>>
>> so why cant sp_databases show it?
>>
>> "Brian Henry" <nospam@nospam.com> wrote in message
>> news:u149Tc$MGHA.3944@tk2msftngp13.phx.gbl...
>>> In sql server 2000 we used sp_databases for users to return a list of
>>> databases (the users were not admins and had no special permissions
>>> besides had rights to a database or two) but in 2005 the non admins when
>>> they execute sp_databases to get a list back, well get nothing back!
>>> what has changed? the only thing i can see is in 2000 they had
>>> permissions like this
>>>
>>> Permissions
>>> Execute permissions default to the public role.
>>>
>>> but in 2005 it says select permission must be on the schema... I REALLY
>>> need to return a database list to users who are not admins... what can I
>>> do? thanks!
>>>
>>>
>>
>>
>
>