1

So what I am trying to do is to limit the access to a stored procedure so that only another stored procedure may call it. Here is an example:

create procedure Database_Deployment_Proc      -- This procedure can be executed, 
                                               -- but "view definition" is restricted
    @databaseName nvarchar(200)
as
begin
    exec Internal_DeployTables      @databaseName   -- Internal_ should be inaccessible 
                                                    -- if executed outside Database_Deployment_Proc
    exec Internal_DeployConstraints @databaseName
    exec Internal_DeployIndexes     @databaseName
    exec Internal_DeployProcedures  @databaseName
    exec Internal_DeployFunctions   @databaseName
end

My goal is to deny any access to any database component, with exceptions of when it is called from specific stored procedure. Is it possible?

I understand that this approach is very bad for the query plan, but it will ease a lot of my work for deploying same database to different machines, with different users in different domains (unrelated).

Is it better to keep it in stored procedure or embed it in C# CLR assembly?

Thanks in advance!

2

Permissions are enough to handle this requirement: just GRANT permissions on the wrapper stored procedure and you should be fine. Don't GRANT permissions on the Internal_* stored procedures and users won't be able to execute them.

Example:

USE tempdb;
GO

CREATE USER ZeroCool WITHOUT LOGIN;
GO

CREATE PROC Select1
AS
SELECT 1
GO

CREATE PROC Select2
AS 
SELECT 2
GO

CREATE PROC SelectAll
AS
EXEC Select1
EXEC Select2
GO

GRANT EXECUTE ON object::SelectAll TO ZeroCool
GO

EXECUTE AS USER = 'ZeroCool'

-- Works
EXEC SelectAll
-- Fails
EXEC Select1

REVERT
GO
  • I considered that solution but I was not sure whether it would seal the internal stored procedures. Thanks! – Zlatin Todorinski Nov 23 '15 at 10:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.