Why Login in Azure SQL DB have privilege to select DB tables without linking user to DB?

Question

There are some tables in DB1. I created Login MyLogin in Master DB without creating user for this(or I created but deleted). But now, at least I checked that there is no user with same SID with MyLogin. Why I still can have privilege to search the tables?

select * from sys.sysusers;   -- Check in DB1 
select * from sys.sql_logins; -- Check in Master DB    

Answer 1

Before we create login, we need to check if the login has exist. Demo code:

DROP LOGIN TestLogin;  
GO  

CREATE LOGIN TestLogin   
WITH PASSWORD = 'SuperSecret52&&', SID = 0x241C11948AEEB749B0D22646DB1A19F2;  

SELECT * FROM sys.sql_logins WHERE name = 'TestLogin';  
GO  

Reference:CREATE LOGIN (Transact-SQL).

After creating a login, the login can connect to SQL Database but only has the permissions granted to the public role.

But when you performed ALTER AUTHORIZATION ON DATABASE ::[DB1] TO [MyLogin],then ALTER AUTHORIZATION ON DATABASE ::[DB1] TO [ServerAdmin]. It means that change the owner of a SQL Database to MyLogin.

Please see:ALTER AUTHORIZATION for databases.

I think that's why you still can have privilege to search the tables.

Hope this helps.