c# - SQL Permissions differ in SSMS and ASP.NET? -

i have created schema , user called "webapi" used asp.net project stock levels.

so, have simple stored procedure:

select p.productcode,         sum( sl.qty ) 'qty' dbo.stocklevel sl inner join dbo.product p on p.productid = sl.productid group p.productcode 

i have executed fine under ssms using "webapi" user works fine.

but executing within asp.net error message of:

the select permission denied on column 'productid' of object 'product", database 'testingdb', schema 'dbo'. 

here 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 grant read access on productid column fix i'm wondering why ssms allowed execute stored procedure asp.net isn't.

i thought granting execute on stored procedure enough don't have expose table information explicitly.

what need sign procedure.

first, setup:

use test go if exists (select * sys.syslogins name = 'usera')     drop login usera  create login usera password = 'welcome' if exists (select * sys.syslogins name = 'userb')     drop login userb  create login userb password = 'welcome' if exists (select * sys.syslogins name = 'userc')     drop login userc  create login userc password = 'welcome'  if exists (select * sys.tables name = 'customers' , schema_name(schema_id) = 'schemaa')     drop table schemaa.customers if exists (select * sys.schemas name = 'schemaa')     drop schema schemaa if exists (select * sys.sysusers name = 'usera')     drop user usera  if exists (select * sys.tables name = 'orders' , schema_name(schema_id) = 'schemab')     drop table schemab.orders if exists (select * sys.procedures name = 'getcustomerorderinfo' , schema_name(schema_id) = 'schemab')     drop procedure schemab.getcustomerorderinfo  if exists (select * sys.schemas name = 'schemab')     drop schema schemab if exists (select * sys.sysusers name = 'userb')     drop user userb  if exists (select * sys.sysusers name = 'userc')     drop user userc  create user usera login usera alter role db_owner add member usera go create schema schemaa authorization usera go create user userb login userb alter role db_owner add member userb go create schema schemab authorization userb go create user userc login userc  create table schemaa.customers (id int identity)  create table schemab.orders (id int identity, customerid int) go create procedure schemab.getcustomerorderinfo  select  *    schemab.orders o join    schemaa.customers c on      c.id = o.customerid go 

we want userc able execute schemab.getcustomerorderinfo procedure. let's give userc execute permission on procedure:

grant execute on schemab.getcustomerorderinfo userc execute login = 'userc' exec schemab.getcustomerorderinfo  -- select permission denied on object 'customers', database 'test', schema 'schemaa'. revert 

this wasn't enough. can create certificate in database, database user on certificate, give user appropriate permissions (db_owner role in sample), , sign procedure certificate:

create certificate cert_raiser     encryption password = 'pgfd4bb925dgvbd2439587y'     subject = 'raiser',      expiry_date = '01/01/2114'; go  create user cert_user certificate cert_raiser go  alter role db_owner add member cert_user go  add signature schemab.getcustomerorderinfo     certificate cert_raiser     password = 'pgfd4bb925dgvbd2439587y'; go 

it should work ok now.

points make: user created on certificate cannot used normal user, there no login , it's not security problem; permissions give user added context in procedure executed when add signature; if alter procedure, have sign again.