Select permission 2

Question

Hello!

Please excuse me for almost the same question as "Select permission" but there's one thing I'd like to be cleared up:

I see that I can't query a user database (at least the databases for which the Guest account is disabled) until the user mapping between an sql login and a db user is created...

...but what I still do not understand is why does the fn_my_permissions function applied to a user db (Resources) does show SELECT permission for the user (Contoso1\exchadmin) which has NOT YET been mapped to???

use msdb
go
EXECUTE AS USER = 'Contoso1\exchadmin' 
select Name, physical_name, state_desc, (size)*8/1024, growth from msdb.sys.database_files ORDER BY Name
select Name, physical_name, state_desc, (size)*8/1024, growth from Resources.sys.database_files ORDER BY Name
SELECT * FROM fn_my_permissions('msdb.sys.database_files', 'object')
SELECT * FROM fn_my_permissions('Resources.sys.database_files', 'object')
Revert

Thank you in advance,
Michael

Answer 1

I guess this is because names in the sys schema resolves to the resource database. (That is, mssqlsystemresource, not the one you call resource!)

I tried this:

CREATE LOGIN Villevalle WITH PASSWORD = 'LÄÖÄF"?=='
EXECUTE AS LOGIN = 'Villevalle'
go
SELECT * FROM sys.fn_my_permissions('somedatabase.sys.tables', 'OBJECT')
SELECT * FROM sys.fn_my_permissions('somedatabase.dbo.sometable', 'OBJECT')
go
REVERT
DROP LOGIN Villevalle

The first SELECt did indeed return a row with SELECT permission. But the other query produced an error message:

Msg 916, Level 14, State 2, Line 89 The server principal "Villevalle" is not able to access the database "somedatabase" under the current security context.