Permission checking for SQL Server

[Djuka]

Hello,

I used your script to form an API for Microsoft SQL Server. The problem occured when DELETE request on an entity constantly returned null. I debugged and concluded that SQL query and params were all OK. Finally I realized that script was unable to delete because the user I was using didn't have adequate permissions to delete rows in that table.

Using same user I connected to database using Microsoft SQL Server Management Studio and ran a query I copied from answer on StackOverflow:

https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database

The result was really good. It was easy to see all permission I had for the user I logged in. Here is some data (I swapped rows and columns so they could fit here):

Columns	Row 1	Row 2
UserName	guest	test_user
UserType	SQL User	SQL User
DatabaseUserName	guest	test_user
Role	NULL	NULL
PermissionType	EXECUTE	SELECT
PermissionState	DENY	GRANT
ObjectType	SQL_STORED_PROCEDURE	USER_TABLE
ObjectName	sp_helpdiagramdefinition	test_table
ColumnName	NULL	NULL

I would suggest a query could be made to check the permissions before doing query on a specific table. If the specific permission is not assigned to user API would return HTTP response 403 Forbidden. This way permissions could be managed on SQL Server.

My knowledge and experience with SQL Server is limited. This sounds convenient to me, but I am not sure if it really is.

P.S. Wonderfull work you've done here

[mevdschee]

Thank you for your kind words. Unfortunately I cannot map all failure cases for queries on the correct error codes and doing an extra permission check is too expensive. That's why you get no specific failure

[mevdschee]

Version 2 has a little better error handling and this may be implemented in v2