Assigning permissions

Hello,
I have the following:
schema: xproject
Database Role: WebPublisher
User: MyUserHandle
sproc: xproject.MyProcedure
I added the role "WebPublisher" to the schema "xproject" with
Delete
Execute
Insert
Select
Update
I added the user "MyUserHandle" to the database role "WebPublisher"
When I tried to execute the stored procedure from an ASP.NET page, I got the
error
EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
schema 'xproject'
However, when I added the user to the schema and gave it the same
permissions as the database role, the page rendered.
My question is why do I have to assign the user to the schema when the
database role already has the user?
Thanks, sck10
Hi
"sck10" wrote:

> Hello,
> I have the following:
> schema: xproject
> Database Role: WebPublisher
> User: MyUserHandle
> sproc: xproject.MyProcedure
> I added the role "WebPublisher" to the schema "xproject" with
> Delete
> Execute
> Insert
> Select
> Update
> I added the user "MyUserHandle" to the database role "WebPublisher"
>
> When I tried to execute the stored procedure from an ASP.NET page, I got the
> error
> EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
> schema 'xproject'
This would imply that the user is not actually a member of the role. See
below:

> However, when I added the user to the schema and gave it the same
> permissions as the database role, the page rendered.
I am not sure what you mean by adding the "user to the schema", granting the
permission to execute the procedure to the user will allow you to execute the
procedure. See below:

> My question is why do I have to assign the user to the schema when the
> database role already has the user?
No, see below:
> Thanks, sck10
>
SELECT @.@.VERSION
GO
/*
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
*/
CREATE DATABASE TestPermissions
GO
USE TestPermissions
GO
CREATE SCHEMA xproject
GO
CREATE ROLE WebPublisher
GO
GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
WebPublisher
GO
CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
varchar(10) not null )
GO
CREATE Procedure xproject.MyProcedure
AS
SELECT id, valuetext FROM mytable
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
CREATE USER MyUserHandle WITHOUT LOGIN
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername =
'MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername =
'MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
USE MASTER
GO
DROP DATABASE TestPermissions
GO
John
|||thanks for the example John,
this helps greatly.
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AB4CE95C-EF32-4431-9D6B-B4872796A1FF@.microsoft.com...
> Hi
> "sck10" wrote:
> This would imply that the user is not actually a member of the role. See
> below:
> I am not sure what you mean by adding the "user to the schema", granting
> the
> permission to execute the procedure to the user will allow you to execute
> the
> procedure. See below:
> No, see below:
> SELECT @.@.VERSION
> GO
> /*
> Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
> Mar 23 2007 16:28:52
> Copyright (c) 1988-2005 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> */
> CREATE DATABASE TestPermissions
> GO
> USE TestPermissions
> GO
> CREATE SCHEMA xproject
> GO
> CREATE ROLE WebPublisher
> GO
> GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
> WebPublisher
> GO
> CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
> varchar(10) not null )
> GO
> CREATE Procedure xproject.MyProcedure
> AS
> SELECT id, valuetext FROM mytable
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> CREATE USER MyUserHandle WITHOUT LOGIN
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername =
> 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername =
> 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> USE MASTER
> GO
> DROP DATABASE TestPermissions
> GO
> John
>