While developing a web-based application today I received an error message from SQL Server:
SELECT permission denied on object ‘Pit’, database ‘TG_ReportingSystem’, owner ‘dbo’. SELECT permission denied on object ‘IssueLogsType’, database ‘TG_ReportingSystem’, owner ‘dbo’. SELECT permission denied on …
This was initially confusing, as all my tables are owned by dbo, and all stored procedures are owned by dbo. The web user’s role had been granted execute permissions to the stored procedure, but it was still checking the permissions.
The cause? I’d used sp_executesql inside the stored procedure to execute some dynamic SQL, so the chain of ownership had broken. When running sp_executesql, it executes under the caller’s security settings, and so my dynamic select statement failed as the user’s role doesn’t have direct access to the tables.
The solution? There were three main solutions to this:
- Remove the dynamic SQL and so not require sp_executesql
- Grant SELECT permissions on the five tables
- Use a view that encapsulates the five tables that the dynamic SQL joins together, and grant the user’s role access to this view
The first option wasn’t really a solution without drastically increasing the complexity of the statement. The second one is simple and only provides information that the user can see through the web interface anyway.
The view would be a perfect solution, except that one of the tables is joined dynamically via a LEFT OUTER JOIN. In this case one solution would be to add this join to the view so it is used every time, but ensure that the join order specifies that the table should be joined as late as possible, as it is the only table to provide a one-to-many relationship. The other solution would be to keep this table joined dynamically, but grant SELECT access to this table.
In the end, the second option was chosen: the five tables don’t expose any data that any users wouldn’t be able to see anyway, and the view solution provides more complexity and may still require SELECT access to one of the tables.