locked
Need to allow user to view all logins on SQL Server RRS feed

  • Question

  • SQL Server 2005/2008.

    By default, on SQL Server any user can see only his login or NT Groups, which he belongs to (both through Security\Logins in SSMS and sys.server_princapals DMV). I need to permit one power user to see all SQL Server logins. But only see, not change. Can you help me?

    P.S. VIEW ANY DEFINITION helps, but this permissions are too much.

    Monday, September 6, 2010 10:46 AM

Answers

  • Hi Sergey,

     

    In this situation, we could just grant VIEW DEFINITION permission on specified login to this login.

    For more information about how to grant this permission to specified login, you could refer to this link: http://msdn.microsoft.com/en-us/library/ms178640.aspx

    Meanwhile I have attached the test script to the end of this reply, you could refer to it.

     

    USE master

    GO

    CREATE LOGIN <login name> WITH PASSWORD = 'abcd123+'

    GO

    GRANT VIEW DEFINITION ON LOGIN::<login which will be viewed> TO <login name>

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Tuesday, September 7, 2010 9:22 AM

All replies

  • You can possibly write a stored procedure in Master database and add the specific user to the master database as a USER and grant him exec rights on the SP. By only granting him access to the SP you can stop other normal users from seeing the logins that they shouldn’t be.

     

    Sample:

     

    create login Leks with password ='Leks123'

    go

    use master

    go

    create user leks for login leks

    go

     

    -- Now try to execute sys.server_principal

     

    execute as login = 'leks'

    select * from sys.server_principals where type_desc not like 'server_role'

    go

    revert

    go

     

    CREATE PROCEDURE syslogins_impersonate_for_LEKS

    WITH EXECUTE AS OWNER

    AS

    BEGIN

    SELECT * from sys.server_principals where type_desc not like 'server_role'

    END;

    GO

     

    -- Grant only access to user LEKS and not to rest of the normal users

     

    Grant exec on syslogins_impersonate_for_leks to Leks

     

    -- Again try to execute sys.server_principal through the new Sp created

     

    execute as login = 'leks'

    go

    exec syslogins_impersonate_for_leks

    go

    revert

    go


    Thanks, Leks
    Monday, September 6, 2010 10:45 PM
  • Thanks, Lekss. But it is not what I'm looking for. The user should see all logins through SSMS Security\Logins or sys.server_principals. It seems my task does not have solution.
    Tuesday, September 7, 2010 7:26 AM
  • I do not think there is such magic role in SQL Server to see all logins but not chaging them......
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, September 7, 2010 7:36 AM
  • Hi Sergey,

     

    In this situation, we could just grant VIEW DEFINITION permission on specified login to this login.

    For more information about how to grant this permission to specified login, you could refer to this link: http://msdn.microsoft.com/en-us/library/ms178640.aspx

    Meanwhile I have attached the test script to the end of this reply, you could refer to it.

     

    USE master

    GO

    CREATE LOGIN <login name> WITH PASSWORD = 'abcd123+'

    GO

    GRANT VIEW DEFINITION ON LOGIN::<login which will be viewed> TO <login name>

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Tuesday, September 7, 2010 9:22 AM
  • Hi Sergey,

     

    In this situation, we could just grant VIEW DEFINITION permission on specified login to this login.

    For more information about how to grant this permission to specified login, you could refer to this link: http://msdn.microsoft.com/en-us/library/ms178640.aspx

    Meanwhile I have attached the test script to the end of this reply, you could refer to it.

     

    USE master

    GO

    CREATE LOGIN <login name> WITH PASSWORD = 'abcd123+'

    GO

    GRANT VIEW DEFINITION ON LOGIN::<login which will be viewed> TO <login name>

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li

    Thanks, Tom. That's what I was looking for!
    Thursday, September 9, 2010 6:02 AM