MSSQL: give non-privileged user access to information_schema?
-
I have a stored procedure. It selects from Information_Schema for ~~rEaSoNs~~.
I have a standard user with standard security that the website uses to access the database. Every interaction that user has is through stored procedures.
When the standard user runs the stored procedure, it does not do the select from information_schema.
If I log in as sa, is there a way to tell the database "yo, my dude, it's okay if this one stored proc accesses information_schema, even if the user executing the stored proc can't." ?
-
@Lorne-Kates
The usual recommended approach for system-adminy-things without sa permissions is:- Create a stored procedure that does the task/query that requires SA permissions and use EXECUTE AS to allow it to execute in the SA context
- Sign the stored procedure with a certificate
- Grant the desired user(s) permissions to execute the stored procedure
There's also some extra consideration for controlling or removing access to the certificate so people can't create new procedures that get more permissions than intended. This blog post looks like a reasonable starting point, from a quick one-over; though I would encourage you to do more research before just blindly following me/it, I spent like 5 minutes tops writing this post
Using a Certificate to Sign a Stored Procedure
And how to assign Server-Level permissions to a database user. Background In a previous article I gave an example of assigning permissions to a user and making use of the EXECUTE AS instruction. Th…
