execute permissions aspnet sql server 2005

Ive created a DAL called Artist.xsd. Ive used stored procedures to access the data. The wizard created a stored procedure called 'dbo.ArtistSelectCommand' Ive granted the ASPNET account execute permissions on this stored procedure When I run the application and try to execute the stored proc, I get this error

EXECUTE permission denied on object 'ArtistSelectCommand', database 'EBSNet', owner 'dbo'.

as far as im aware ive givne the ASPNET account the correct permissions

EXEC

sp_grantlogin [MachineName\ASPNET]

EXEC

sp_grantdbaccess [MachineName\ASPNET], [ASPNET]

** Ive also done this by selecting the stored proc in sql server 2005 and setting permissions by right clicking and selecting properties.

Is there anything else I need to do ?

Try this:

GRANT EXEC on <storedproc> TO <machinename\user>

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)
>

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)
>

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)
>

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 symin:
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 symin:
> 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)
>

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)
>