March 16, 2016 by Kenneth Fisher
Ever wondered how to tell what permissions are required to run a statement or perform a task? Well, sometimes it can be a little confusing to be honest. Most of the time though it’s all there in front of you in black and white.
Where you might ask? Books on line! I’ve talked before about the importance of being familiar with BOL, and here we are again. The pages about commands, and system objects (views, functions, stored procedures etc) are broken into multiple different sections.
- Syntax – Where appropriate the format of the command or parameters of the stored procedure/function.
- Arguments – A detailed description of the different pieces of the command.
- Columns – Where appropriate the columns of the output.
- Remarks – Various information. Discussions about related tasks etc. For example the Remarks section of CREATE TABLES has information on constraints, partitioned tables, foreign keys etc.
- Permissions – Ding! Ding! Ding! The permissions required for a command!
- Examples – Examples of different applications of the command.
Down near the bottom of the majority of these pages is a Permissions section. This section will tell you what permissions are required to perform the command/task. Sometimes you’ll find additional permissions information in other sections. For example if there are permissions implications of the command itself. The EXECUTE AS page is mostly about permissions, for obvious reasons.
So if you need to figure out what permissions are needed, step one is to find the Books on Line page for the command or object. Then look at the Permissions section and if needed skim the rest the page. This isn’t always going to get your answer, but it will the vast majority of the time.