execute permission denied on schema?

Boy, this one is tangled, but I'll take a guess at what's relevant.
I create a schema to hold an XSD, use it, it's all great - in dev.
Trying to deploy to staging with stricter security, something is
wrong.
-- this works fine, executed by someone with sysadmin:
declare @.RBSchema XML
set @.RBSchema = (select * from openrowset(bulk
'D:\RC850\RB850.xsd', single_clob) as xmlData)
create xml schema collection RB850Schema as @.RBSchema
-- at some point, executing as someone with minimal privileges,
-- I get this message:
/*
EXECUTE permission denied on object 'RB850Schema',
database 'mydb', schema 'dbo'.
*/
So, it looks like I need to grant something to somebody, but this
fails:
GRANT EXECUTE ON dbo.RB850Schema TO minimaluser
GO
/*
Msg 15151, Level 16, State 1, Line 1
Cannot find the schema 'RB850Schema',
because it does not exist or you do not have permission.
*/
Now, I just (re)created it and it works, but, well, so, what's the
deal?
Thanks.
Josh
ps - complicating factors might be that this is happening in an SSIS
environment, which was working fine when it used integrated security,
but is now sticking with standard security. Also, I'm not certain
exactly where it's generated, but it may be from a dynamic SQL
statement like:
SET @.cmd = 'DECLARE @.x850 XML (RB850Schema)
SET @.x850 = (select * from openrowset(bulk '''
+ @.xmlfilename + ''' , single_clob) as xmldata);
SELECT @.X850 AS myxml;'
INSERT INTO @.mytable (myxml)
execute @.rc = sp_executesql @.cmd
(again, this works fine, until I try to limit privileges)Answering my own question:
By clicking around under database security, selecting objects by type, and
hitting the script button on the dialog, I got:
GRANT EXECUTE ON XML SCHEMA COLLECTION::[dbo].[RB850Schema] TO [minimaluser]
GO
Don't know quite yet if it fixes everything, but at least the grant executes!
So, who exactly makes up this syntax?
Talking to himself in public yet again,
Josh
"JXStern" wrote:
> Boy, this one is tangled, but I'll take a guess at what's relevant.
> I create a schema to hold an XSD, use it, it's all great - in dev.
> Trying to deploy to staging with stricter security, something is
> wrong.
> -- this works fine, executed by someone with sysadmin:
> declare @.RBSchema XML
> set @.RBSchema = (select * from openrowset(bulk
> 'D:\RC850\RB850.xsd', single_clob) as xmlData)
> create xml schema collection RB850Schema as @.RBSchema
> -- at some point, executing as someone with minimal privileges,
> -- I get this message:
> /*
> EXECUTE permission denied on object 'RB850Schema',
> database 'mydb', schema 'dbo'.
> */
> So, it looks like I need to grant something to somebody, but this
> fails:
> GRANT EXECUTE ON dbo.RB850Schema TO minimaluser
> GO
> /*
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the schema 'RB850Schema',
> because it does not exist or you do not have permission.
> */
> Now, I just (re)created it and it works, but, well, so, what's the
> deal?
> Thanks.
> Josh
> ps - complicating factors might be that this is happening in an SSIS
> environment, which was working fine when it used integrated security,
> but is now sticking with standard security. Also, I'm not certain
> exactly where it's generated, but it may be from a dynamic SQL
> statement like:
> SET @.cmd = 'DECLARE @.x850 XML (RB850Schema)
> SET @.x850 = (select * from openrowset(bulk '''
> + @.xmlfilename + ''' , single_clob) as xmldata);
> SELECT @.X850 AS myxml;'
> INSERT INTO @.mytable (myxml)
> execute @.rc = sp_executesql @.cmd
> (again, this works fine, until I try to limit privileges)
>