SQL Permissions differ in SSMS and ASP.NET?

Question

I have created a schema and user called "WebAPI" which is used by ASP.NET project to get stock levels.

So, I have simple stored procedure:

SELECT p.ProductCode,
        SUM( sl.Qty ) AS 'Qty'
FROM dbo.StockLevel sl
INNER JOIN dbo.Product p ON p.ProductID = sl.ProductID
GROUP BY p.ProductCode

I have executed this fine under SSMS using "WebApi" user which works fine.

But executing this within ASP.NET I get error message of:

The SELECT permission was denied on the column 'ProductID' of the object 'Product", database 'TestingDb', schema 'dbo'.

Here is the code I'm using in ASP.NET:

    string connectionString = ConfigurationManager.AppSettings["ConnectionString"]; 

    using (var conn = new SqlConnection(connectionString))
    {
        using (var cmd = conn.CreateCommand())
        {
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "[webapi].[GetStock]";

            using (var reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    var objTmp = new StockReportEntity();
                    objTmp.ProductCode = (string)reader["ProductCode"];
                    objTmp.Qty = (decimal) reader["Qty"];
                    objRet.Add(objTmp);

                }
            }
        }
    }

I know I could just grant read access on ProductID column to fix this but I'm wondering why SSMS is allowed to execute the stored procedure but ASP.NET isn't.

I thought granting execute on the stored procedure would be enough so I don't have to expose any table information explicitly.

Answer 1

What you need is to sign the procedure.

First, the setup:

use Test
go
if exists (select * from sys.syslogins where name = 'UserA')
    drop login UserA 
create login UserA with password = 'Welcome'
if exists (select * from sys.syslogins where name = 'UserB')
    drop login UserB 
create login UserB with password = 'Welcome'
if exists (select * from sys.syslogins where name = 'UserC')
    drop login UserC 
create login UserC with password = 'Welcome'

if exists (select * from sys.tables where name = 'Customers' and schema_name(schema_id) = 'SchemaA')
    drop table SchemaA.Customers
if exists (select * from sys.schemas where name = 'SchemaA')
    drop schema SchemaA
if exists (select * from sys.sysusers where name = 'UserA')
    drop user UserA

if exists (select * from sys.tables where name = 'Orders' and schema_name(schema_id) = 'SchemaB')
    drop table SchemaB.Orders
if exists (select * from sys.procedures where name = 'GetCustomerOrderInfo' and schema_name(schema_id) = 'SchemaB')
    drop procedure SchemaB.GetCustomerOrderInfo 
if exists (select * from sys.schemas where name = 'SchemaB')
    drop schema SchemaB
if exists (select * from sys.sysusers where name = 'UserB')
    drop user UserB

if exists (select * from sys.sysusers where name = 'UserC')
    drop user UserC

create user UserA for login UserA
alter role db_owner add member UserA
go
create schema SchemaA authorization UserA
go
create user UserB for login UserB
alter role db_owner add member UserB
go
create schema SchemaB authorization UserB
go
create user UserC for login UserC

create table SchemaA.Customers (id int identity)

create table SchemaB.Orders (id int identity, CustomerId int)
go
create procedure SchemaB.GetCustomerOrderInfo 
as
select  *
from    SchemaB.Orders o
join    SchemaA.Customers c
on      c.id = o.CustomerId
go

We want UserC to be able to execute the SchemaB.GetCustomerOrderInfo procedure. Let's give the UserC execute permission on the procedure:

grant execute on SchemaB.GetCustomerOrderInfo to UserC
execute as login = 'UserC'
exec SchemaB.GetCustomerOrderInfo 
-- The SELECT permission was denied on the object 'Customers', database 'Test', schema 'SchemaA'.
revert

This wasn't good enough. What we can do is create a certificate in the database, a database user on this certificate, give that user appropriate permissions (db_owner role in this sample), and then sign the procedure with the certificate:

create certificate cert_raiser
    encryption by password = 'pGFD4bb925DGvbd2439587y'
    with subject = 'raiser', 
    expiry_date = '01/01/2114';
go

create user cert_user from certificate cert_raiser
go

alter role db_owner add member cert_user
go

add signature to SchemaB.GetCustomerOrderInfo 
   by certificate cert_raiser
    with password = 'pGFD4bb925DGvbd2439587y';
go

It should work OK now.

Points to make: the user created on the certificate cannot be used as a normal user, there is no login with it and it's not a security problem; all the permissions we give that user will be added to context in which the procedure is executed when we add a signature; If we alter the procedure, we have to sign it again.