Sql-server – SELECT permission was denied on the object

rolesSecuritysql server

If have created a custom role within SqlServer which I added to the db__denydatareader and db__denydatawriter roles. I think went through the db and granted exec permission to all neccersary stored procedures.

Everything works fine, calling those sps will run fine. The one exception is a stored procedure which executes dynamic sql by using sp_executesql. This fails saying

The SELECT permission was denied on the object 'listing_counter', database 'Cannla', schema 'dbo'.

Is there any way to grant the role permission to run this query without giving it select access to the underlying tables?

I guess what I'm wanting to do is grant exec on sys.sp_executesql but only in a certain case.

Best Answer

You can create a new user just for your stored procedure that uses execute_sql, grant him the requires rights and then add to the procedure definition WITH EXECUTE AS 'MyUser'. See MSDN.

Related Solutions

Sql – How to perform an IF…THEN in an SQL SELECT

The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server.

SELECT CAST(
             CASE
                  WHEN Obsolete = 'N' or InStock = 'Y'
                     THEN 1
                  ELSE 0
             END AS bit) as Saleable, *
FROM Product

You only need to use the CAST operator if you want the result as a Boolean value. If you are happy with an int, this works:

SELECT CASE
            WHEN Obsolete = 'N' or InStock = 'Y'
               THEN 1
               ELSE 0
       END as Saleable, *
FROM Product

CASE statements can be embedded in other CASE statements and even included in aggregates.

SQL Server Denali (SQL Server 2012) adds the IIF statement which is also available in access (pointed out by Martin Smith):

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product

Sql – The SELECT permission was denied on the object ‘sysobjects’, database ‘mssqlsystemresource’, schema ‘sys’

This was a problem with the user having deny privileges as well; in my haste to grant permissions I basically gave the user everything. And deny was killing it. So as soon as I removed those permissions it worked.

Best Answer

Related Solutions

Sql – How to perform an IF…THEN in an SQL SELECT

Sql – The SELECT permission was denied on the object ‘sysobjects’, database ‘mssqlsystemresource’, schema ‘sys’

Related Topic