With SQL2000, the dbo's were able to see all the logins, so that they could
add them as users to their databases. We just starting setting up SQL
Server 2005 and it came to our attention that they no longer have this
ability. What rights do dbo's need to have in order to view all logins?
Thanks,
sck10Hi,
To let we better understand your issue, could you please tell me more on
this issue so that I can reproduce your issue? You may also mail me
(changliw@.microsoft.com) a screenshot of your issue.
As far as I know, dbo is a database role, while login is for the SQL Server
instance. If you want to see all the logins of a SQL Server, the login
account should be a member of sysadmin. Even in SQL Server 2000, one
database owner cannot run sp_helplogins to see all the logins unless he is
a sysadmin. You can log on your SQL Server instance with a system
administrator and assign the login account with the server role sysadmin,
then try again.
Hope this helps! Please feel free to let us know if you have any other
questions or concerns.
Happy New Year!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Charles,
I sent an email with the snapshots.
I am using Enterprise Mgr 2005 to connect to both a SQL2000 database and a
SQL2005 database. I am the dbo for databases on both machines. With
SQL2000, when I add a user to my database, I can see all the users in the
database. With SQL2005, I can only see myself and the sa.
Thanks again,
sck10
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hi Charles
> As far as I know, dbo is a database role,
I have been always thinking that 'dbo' is 'privileged' user not a database
role.
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||
Uri Dimant wrote:[vbcol=seagreen]
>Hi Charles
>I have been always thinking that 'dbo' is 'privileged' user not a database
>role.
>
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1|||Hi Charles
dbo is NOT a database role, it is a privileged database user. There is a
database role called db_owner, of which the dbo user is always a member.
It's hard enough understanding logins, users and roles, we need to be really
careful to use these terms correctly.
Thanks
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hi Kalen,
Thanks for your pointing it out!
I am sorry for using that wrong words and I appologize for that.
Your explanation is meaningful here. I will pay attention to this from now
on!
Thank you!
Charles Wang
Microsoft Online Community Support|||Hi sck10,
I had sent you an email for this issue.
I reproduced your issue now but I think that the behavior of showing logins
of SQL Server 2000 is not reasonable. I need to consult the SQL Server 2005
product team for this issue and I will let you know their replies as soon
as possible.
If you have any other questions or concerns, please feel free to contact
us. It is always our pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support|||Hi Steven,
I got the response from SQL team. The reason is as following:
This is because of security restrictions to view the metadata in SQL Server
2005. A user can only see metadata that the user either owns or on which
the user has been granted some permission. This policy prevents users with
minimal privileges from viewing metadata for all objects in an instance of
SQL Server 2005.
However if you do not want this security, the following statement can be
used to override metadata-visibility limitations at the instance level. All
metadata in the instance will be visible to the granted user. Doing so
would allow the user to see all other logins not only logins but all
metadata which is not a recommended practice.
GRANT VIEW ANY DEFINITION TO <USERNAME>
The <username> has to be replaced by the actual user who needs this. This
statement has to be executed by sysadmin.
Hope this helps!
Please feel free to let me know if you have any other questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support|||You might also be interested in this article on Metadata Security that I
wrote for technet Magazine:
http://www.microsoft.com/technet/te...p://sqlblog.com
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:NngjapFNHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi Steven,
> I got the response from SQL team. The reason is as following:
> This is because of security restrictions to view the metadata in SQL
> Server
> 2005. A user can only see metadata that the user either owns or on which
> the user has been granted some permission. This policy prevents users with
> minimal privileges from viewing metadata for all objects in an instance of
> SQL Server 2005.
> However if you do not want this security, the following statement can be
> used to override metadata-visibility limitations at the instance level.
> All
> metadata in the instance will be visible to the granted user. Doing so
> would allow the user to see all other logins not only logins but all
> metadata which is not a recommended practice.
> GRANT VIEW ANY DEFINITION TO <USERNAME>
> The <username> has to be replaced by the actual user who needs this. This
> statement has to be executed by sysadmin.
> Hope this helps!
>
> Please feel free to let me know if you have any other questions or
> concerns.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
>
No comments:
Post a Comment