question

acappelletti avatar image
0 Votes"
acappelletti asked Cathyji-msft edited

database acces without user mapping

Hi all,
I just end to create a couple of linked server between SERVER1 and SERVER2.
I used 2 sql server login for mapping some windows user under Linked server setting.

Ok all it's right... but I saw on one side (SERVER1) that there are 2 databases that I haven't activated at all and are selectable from the linked server.
I don't understand why. I try also to login with the service user (that one for the linked server just created on the server and I can select on 3 dbs that I have activated and also these 2 dbs that I have no access for the user)...

Honestly it's something that has never happened to me, I don't understand why... how is it possible? Seem like an open database access for everybody
Alen

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

One possibility is that that the guest user has been activated.

In one of these databases do:

EXECUTE AS LOGIN = 'serviceuser'
go
SELECT * FROM sys.user_token
go
REVERT

This will list all user tokens active in this database. If you only see public, it will have to be guest. If you see more, check roles for the members.

If it is indeed the guest user, you can turn it off with

REVOKE CONNECT TO guest
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI Erland...
I checked... there is no guest active into 2 database and using your sql I got error... but I think is correct 'cause user cannot connect...

USE db
etc...

Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal "svc_XXXXXX" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I created a simple SQL SERVER user only connect and able to use the linked server.... 2 dbs are showed me... as always
Ideas?

0 Votes 0 ·

That should better be EXECUTE AS LOGIN, and I've edited by post to reflect that.

In any case, the important part is to check sys.user_token, and depending on the outcome also sys.login_token. The point with EXECUTE AS is that it saves you from having to log in as the service user in a separate window.

0 Votes 0 ·

I tried also with a restore of one of 2 DBs... and pop-up from my linked server... so I'm sure it's something related a that DBs...

Execute on the one restored
REVOKE CONNECT TO guest

but always is between listing...

ALen

0 Votes 0 ·
Show more comments
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @acappelletti,

after the attach.... from the "linked server" folder, I can see the new DB (copy of the other of which should not have access)

As Erland mentioned, it is normal that you can see the list of all databases.

and naturally get select from that

You can access the two databases( DB4,DB5)? Can you using select query (as below T-SQL)to get data from the two DBs?

 select * from [NODE4\SQL2019].test.dbo.test

Did you map the local login to remote login( login in linked server) as below screenshot ? Did the login on linked server (such as login1) has the permission to access the two DBs ?

98176-screenshot-2021-05-20-155940.jpg







5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.