May 6, 2019 at 1:19 pm
I have a logging stored procedure that need to perform SELECTs from sys.dm_exec_connections and sys.dm_exec_sessions. To do so, the principal requires VIEW SERVER STATE privilege. Obviously, I do not want to grant every user which would execute the stored procedure VIEW SERVER STATE privilege. The stored proc is compile under the SA account. Yet, unless a user who has EXECUTE privilege was explicitly granted VIEW SERVER STATE privilege, the execution of the proc fails with "VIEW SERVER STATE permission was denied on object 'server', database 'master'. The user does not have permission to perform this action." error.
Any suggestions?
May 6, 2019 at 1:46 pm
following article from Solomon Rutzky probably what you looking for https://sqlquantumleap.com/2018/02/15/safely-and-easily-use-high-level-permissions-without-granting-them-to-anyone-server-level/
His CLR related posts are really a must read for anyone that needs to allow users to use "higher privilege" commands without giving them the full access that those permissions would allow.
May 6, 2019 at 4:53 pm
@Frederico: Thank you for that compliment! It was very kind of you to share that, and it is much appreciated 🙂 And yes, that particular post that you linked to is exactly what Zorge needs.
@zorge: regarding your statement of
The stored proc is compile under the SA account.
No, that is not possible. The "sa" account is a login, which is an instance-level principal. Yes, instance-level is what you need to deal with since VIEW SERVER STATE is an instance-level permission, but the EXECUTE AS clause of a CREATE object statement can only reference users, which are database-level principals. The only way to link to a login via the EXECUTE AS clause of a CREATE object statement is to specify a user that is associated with a login and to have enabled TRUSTWORTHY on that database. BUT, enabling TRUSTWORTHY is a security risk, and an unnecessary one given what I show in the post that Frederico linked to (i.e. Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level).
For a thorough overview of how security works, why EXECUTE AS and TRUSTWORTHY ON should be avoided, and why Module Signing should be used, please see:
PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining
Take care, Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply