locked
Database shown under Catalogs of the linked Servers which has not granted with permission RRS feed

  • Question

  • Hi,

    We just add a linked server to S2, for some reason, it shows 2 DBs of S1, one is right, the other is not. Though I don't see the content of the wrong one. But why does this happen, I have so many other DBs in S1, why just this wrong one shows up.

    Please advice. Your help would be greatly appreciated.

    Tuesday, November 20, 2012 4:52 PM

Answers

  • Hi Fedor,

    How do I know if guest is under db_owner? Regardless, I have other DBs in S1 with guest as the user, but they are not listed under the linked server.

    Thanks.

    Please execute it on DB2.

    select d.name 'User', d2.name 'Role'
    from sys.database_principals d inner join sys.database_role_members r on d.principal_id = r.member_principal_id
    inner join sys.database_principals d2 on r.role_principal_id = d2.principal_id
    where d.name = 'guest'




    • Edited by Fedor Pustovachenko Wednesday, November 21, 2012 5:02 PM
    • Marked as answer by wkpli Thursday, November 22, 2012 3:40 PM
    Wednesday, November 21, 2012 4:52 PM
  • Hi, plesae run the following codes to check whether the remote login has user within the database (DB2):

    use DB2;
    go
    
    SELECT dp.name AS user_name
    FROM sys.server_principals sp
    JOIN sys.database_principals dp ON (sp.sid = dp.sid)
    WHERE sp.name = 'RemoteUserName'
    

    you want to remove DB2 under the catalog of the linked server, let’s perform the following codes to drop the user:

    use DB2;
    go
    
    -- UserNamehere is the result we get with the above codes
    drop use UserNamehere
    


    Allen Li
    TechNet Community Support

    • Marked as answer by wkpli Thursday, November 22, 2012 3:40 PM
    Thursday, November 22, 2012 9:03 AM
  • The Owner and Object are . because there is no owner.object (example, dbo.sometable) to which rights are granted.  You can make the 'guest' user go a way, run the following command:

    USE DB2;
    DROP USER [guest];

    You will notice that 'guest' which is a system account still will exist in your user list, but a red down arrow describes it as disabled.

    If you determine that you need 'guest' again, simply:

    USE DB2;
    CREATE USER [guest] WITH DEFAULT_SCHEMA=[guest];
    RLF
    • Marked as answer by wkpli Thursday, November 22, 2012 3:40 PM
    Thursday, November 22, 2012 2:40 PM

All replies

  • Are you seeing duplicate name? What did you mean by "one is right, the other is not" I would suggest to captre profiler and debug it.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Tuesday, November 20, 2012 4:58 PM
  • Not duplicate, but 2 DBs

    DB1 and DB2 are from server S1. We created a user login which can access DB1

    When we add a linked server to server S2, using that user login, it shows not only DB1 but also DB2. The thing is that new login has no access to DB2. But how come it shows under the catalog of the linked server? Not any other DBs in S1, as I have many other DBs in S1.

    Tuesday, November 20, 2012 5:29 PM
  • Is "guest" user in DB2 db_owner?
    Tuesday, November 20, 2012 10:28 PM
  • It simply lists the databases on the server, regardless whether you have access to them or not. Checking whether you have access to a database, would have to be done database by database which could be expensive if there are many in auto-close.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 20, 2012 10:40 PM
  • Hi wkpli,

    If the remote login has user within the database (DB2), we can see the database listed under the catalog of the linked server, even if the remote login has no access to DB2. If you want to remove DB2 under the catalog of the linked server, please open DB2, Security, Users, and remove the user maps to the login.

    TechNet Subscriber Support
    If you are 
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Allen Li
    TechNet Community Support

    Wednesday, November 21, 2012 8:52 AM
  • Hi Fedor,

    How do I know if guest is under db_owner? Regardless, I have other DBs in S1 with guest as the user, but they are not listed under the linked server.

    Thanks.

    Wednesday, November 21, 2012 1:55 PM
  • Hi Fedor,

    How do I know if guest is under db_owner? Regardless, I have other DBs in S1 with guest as the user, but they are not listed under the linked server.

    Thanks.

    Please execute it on DB2.

    select d.name 'User', d2.name 'Role'
    from sys.database_principals d inner join sys.database_role_members r on d.principal_id = r.member_principal_id
    inner join sys.database_principals d2 on r.role_principal_id = d2.principal_id
    where d.name = 'guest'




    • Edited by Fedor Pustovachenko Wednesday, November 21, 2012 5:02 PM
    • Marked as answer by wkpli Thursday, November 22, 2012 3:40 PM
    Wednesday, November 21, 2012 4:52 PM
  • Please check "guest" permissions on DB2

    use DB2
    go
    EXEC sp_helprotect NULL, 'guest'

    It might have CONNECT privilege.

    Wednesday, November 21, 2012 5:09 PM
  • Hi, plesae run the following codes to check whether the remote login has user within the database (DB2):

    use DB2;
    go
    
    SELECT dp.name AS user_name
    FROM sys.server_principals sp
    JOIN sys.database_principals dp ON (sp.sid = dp.sid)
    WHERE sp.name = 'RemoteUserName'
    

    you want to remove DB2 under the catalog of the linked server, let’s perform the following codes to drop the user:

    use DB2;
    go
    
    -- UserNamehere is the result we get with the above codes
    drop use UserNamehere
    


    Allen Li
    TechNet Community Support

    • Marked as answer by wkpli Thursday, November 22, 2012 3:40 PM
    Thursday, November 22, 2012 9:03 AM
  • This is what I got, is that the grant connect causing the problem, but why owner and object has a "." value? How can I remote this permission?

    Owner Object Grantee Grantor ProtectType Action Column
    . . guest dbo Grant      CONNECT .

    Thanks

    Thursday, November 22, 2012 2:12 PM
  • The Owner and Object are . because there is no owner.object (example, dbo.sometable) to which rights are granted.  You can make the 'guest' user go a way, run the following command:

    USE DB2;
    DROP USER [guest];

    You will notice that 'guest' which is a system account still will exist in your user list, but a red down arrow describes it as disabled.

    If you determine that you need 'guest' again, simply:

    USE DB2;
    CREATE USER [guest] WITH DEFAULT_SCHEMA=[guest];
    RLF
    • Marked as answer by wkpli Thursday, November 22, 2012 3:40 PM
    Thursday, November 22, 2012 2:40 PM
  • Thanks everyone!
    Thursday, November 22, 2012 3:40 PM