0

I'm trying to figure out if there is a way to achieve the converse of this: can a SQL Server stored proc execute with higher permission than its caller?

I want to create a stored procedure which does one thing if the user is in a role, but a fallback option if they're not.

My first attempt tried to query the current user's roles, based on this: How to query current user's roles

I tried to query what role a user was in, and decide what to do based on that. But if you set "mydomain\Domain Users" to a role (for example), users who belong to Domain Users aren't listed in the sys.database_role_members view. So users who were supposed to have permissions don't.

From here https://msdn.microsoft.com/en-us/library/ee677633.aspx

IS_ROLEMEMBER always returns 0 when a Windows group is used as the database
principal argument, and this Windows group is a member of another Windows
group which is, in turn, a member of the specified database role.

My next attempt works like this. Create a stored procedure with the actual permissions, and then a wrapper around it which calls the with lower permissions, and if that fails, perform the fallback action:

CREATE PROCEDURE [internal_myproc]
AS
BEGIN
    -- do something here
END
GO

GRANT EXECUTE ON [internal_myproc] TO [Role1] AS [dbo]
GO

CREATE PROCEDURE [myproc]
AS
BEGIN
    BEGIN TRY
        EXEC [internal_myproc]
    END TRY
    BEGIN CATCH
        -- perform fallback action        
    END CATCH
END
GO

GRANT EXECUTE ON [internal_myproc] TO [Role1] AS [dbo]
GO
GRANT EXECUTE ON [internal_myproc] TO [Role2] AS [dbo]
GO
GRANT EXECUTE ON [internal_myproc] TO [Role3] AS [dbo]
GO

The problem with this is that [Role1] and [Role2] both succeed in executing [internal_myproc] via [myproc]. If you take the code out of the stored procedure, it behaves the way it should, but because it's hidden inside a stored procedure, it gets implicit permissions to execute other stored procedures. I've experimented with "WITH EXECUTE AS" stuff, but it doesn't seem to change anything.

I also tried "IF HAS_PERMS_BY_NAME('internal_myproc', 'OBJECT', 'EXECUTE') = 1", suggested here MS SQL Server: Check to see if a user can execute a stored procedure , but that seems to not work in certain situations I haven't understood yet.

Is there a way to NOT grant those implicit permissions, to do a permission check inside a stored procedure? Or something equivalent?

Community
  • 1
  • 1
Bryce Wagner
  • 1,151
  • 7
  • 18
  • Try using an explicit DENY on `internal_myproc` to `Role2` and `Role3`. – Laughing Vergil Mar 21 '17 at 21:53
  • DENY has no effect. And even if it did work, everyone in Role1 will also be in Role2 and maybe in Role3, so that would cause problems for them. – Bryce Wagner Mar 21 '17 at 21:59
  • I kind of gave up on trying to accomplish this in SQL. Instead I just made 2 stored procedures, and the client applications will try the first one first, and fall back on the second one if the first one fails. I'd still welcome an answer if it's possible, though. – Bryce Wagner Mar 24 '17 at 20:53

0 Answers0