I have a strange issue and wish to understand why it works like that, before applying any fix. I need to run SQL queries that are coming from outside and I wish to be sure that those queries can access only what is allowed to access.

It looks basically like this (all happens within milliseconds):

privileged user: create view_guid as select from table
privileged user: grant select permission on view_guid to 'limited user'
limited user: select from view_guid
privileged user: delete view

The problem is, that in prod environment, when limited user tries to query from the view, gets object not found error. I've started to troubleshoot and inserted following query just before running actual query by limited user:

SELECT schema_name(schema_id) AS SchemaName, name AS ViewName FROM sys.views ORDER BY SchemaName, ViewName;

Surprisingly - the view is in the list and more surprisingly - limited user can run his query without a problem.

So I have two hypotheses

• either selecting from sys.views somehow refreshes some metadata cache for limited user
• or it adds small pause, that is enough for view to "appear".

Any ideas what is going on and what could be non hacky solution?

I have a strange issue and wish to understand why it works like that, before applying any fix. I need to run SQL queries that are coming from outside and I wish to be sure that those queries can access only what is allowed to access.

It looks basically like this (all happens within milliseconds):

privileged user: create view_guid as select from table
privileged user: grant select permission on view_guid to 'limited user'
limited user: select from view_guid
privileged user: delete view

The problem is, that in prod environment, when limited user tries to query from the view, gets object not found error. I've started to troubleshoot and inserted following query just before running actual query by limited user:

SELECT schema_name(schema_id) AS SchemaName, name AS ViewName FROM sys.views ORDER BY SchemaName, ViewName;

Surprisingly - the view is in the list and more surprisingly - limited user can run his query without a problem.

So I have two hypotheses

• either selecting from sys.views somehow refreshes some metadata cache for limited user
• or it adds small pause, that is enough for view to "appear".

Any ideas what is going on and what could be non hacky solution?

• sql-server
• azure-sql-database

• What is the exact error the limited user get? – siggemannen Commented Feb 4 at 16:18

1 Answer 1

When a privileged user creates a view and grants SELECT permission, SQL Server may not immediately update all metadata caches. This can result in a scenario where a limited user’s permission check does not immediately reflect the granted access due to SQL Server’s internal security cache.

Querying sys.views forces SQL Server to access its metadata, which can potentially trigger a refresh of the limited user's permissions or visibility. This ensures that the newly granted SELECT permission is recognized and applied as expected.

Use sp_refreshview After Creation of view

EXEC sp_refreshview 'view_name';

Ensures metadata is up-to-date before the limited user queries it.