I have two schemas, one contains tables, stored procedures, general data (will call it data schemas from now on). The second schema has nothing but stored procedures inside of it and we'll call this execute schema.
Now the point of the execute schema is so that I can create a user (we'll call this external user), which only has access to this schema and can only execute stored procedure within the execute schema... no select, update, insert, delete nothing (there are no tables anyway in the execute schema) and has zero access to the data schema. This way the user will be very restricted in what it can do.
The point in this is that, when a stored procedure is created with the "execute schema" it elevates the user rights depending on what is required, without actually giving the external user any rights past that execution.
All is well, and I have it executing and get data from the data schema with a test store procedure. I'm now trying to allow one of the stored procedure in the execute schema to use a user-defined table type, which has been created on the data schema. Due to the user restrictions of not being able to see anything of the data schema it is proving to be a problem.
Now I could recreate the data type or move it to a shared schema... but this is a live old database and ideally I don't want to duplicate types or have to move user-defined stuff every time I wish to allow access to the external user.
So I thought about using Aliases. although when I try to create said alias I get an error:
command:
CREATE TYPE [execute].[udtype1] FROM [data].[udtype1] NOT NULL;
error:
The base type "data.udtype1" is not a valid base type for the alias data type.
While I don't know how to do it, I have noticed that the microsoft documentation creates an utf8string and references it (I'm hoping this means what I want to do, can be done and exists):
CREATE ASSEMBLY utf8string
AUTHORIZATION [dbi]
FROM 0x4D... ;
GO
CREATE TYPE Utf8String
EXTERNAL NAME utf8string.[Microsoft.Samples.SqlServer.utf8string] ;
GO
Does anyone know how to create an alias for a user-defined table, or have an alternative approach.
CodePudding user response：
Quote from comment on the original question:
Alias data types are for scalar data types, you can't create an alias data type of a table type. You'll need to give explicit access to the types on the schema here so that the USER's can declare a parameter of the type. –
User: Larnu
He gave me a couple of keywords which helped with my google search.
It is still weird though, as I had allow execution and after giving them rights on the specific store procedure, I could then remove the rights and it works. Trying to remove the rights first results in the execution failing.