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!