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.

Answer 2

My repro above was a little confusing, because when I anonymised the table and database name, I was inconsistent.

When I tested a little more I found something which is a little puzzling. Look at this:

CREATE DATABASE tester
go
USE tester
go
CREATE TABLE testtbl (a int NOT NULL)
go
USE tempdb
go
CREATE LOGIN testlogin WITH PASSWORD = 'ThisWeeksWeakPassword?'
EXECUTE AS LOGIN = 'testlogin'
go
PRINT 'sys.tables'
SELECT * FROM sys.fn_my_permissions('tester.sys.tables', 'OBJECT')
go
PRINT 'dbo.testtbl'
SELECT * FROM sys.fn_my_permissions('tester.dbo.testtbl', 'OBJECT')
go
PRINT 'dbo.nosuchtbl'
SELECT * FROM sys.fn_my_permissions('tester.dbo.nosuchtbl', 'OBJECT')
go
REVERT
DROP LOGIN testlogin
DROP DATABASE tester

The first and last SELECT runs without error. It is only if you are querying about an actual object that you get an error.