Appliction role Problem with Stored Procedure

Hi,
I'm new using Application Role. I use SQL Query Analyzer to try some
example.
I have create a Application role call "ABC" and have set permission to all
tables & stored procedures.
In Query Analyzer, after setting the role using sp_setapprole,
i) I try to run the stored procedure but it give me 0 result. (it
should return me some records which it doesn't)
ii) I try to run a select statement for a table, it return me all
records.
Since Application Role allows me to set permission for Stored Procedures &
tables, I thought both should be the same.
Does anyone know why is this so?
Regards
VanessaDo you mean that ii is working and i is not? Do you receive any errors?
Try executing the stored procedure code manually.
Or post both i and ii code here.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:

> Hi,
> I'm new using Application Role. I use SQL Query Analyzer to try some
> example.
> I have create a Application role call "ABC" and have set permission to all
> tables & stored procedures.
> In Query Analyzer, after setting the role using sp_setapprole,
> i) I try to run the stored procedure but it give me 0 result. (it
> should return me some records which it doesn't)
> ii) I try to run a select statement for a table, it return me all
> records.
> Since Application Role allows me to set permission for Stored Procedures &
> tables, I thought both should be the same.
> Does anyone know why is this so?
> Regards
> Vanessa
>
>|||For stored procedure, i did not receive any errors but it return me zero
result. If i didn't set application role and run the SP, it return me 55
rows of record.
In the Query Analyzer, the statement i run is
Step 1) EXEC sp_setapprole 'EMERGE', 'test'
Step 2 SP ) Exec aneadm.uspGetAppAllSetting
Step 2 Normal Select Statement) select * from aneadm.tblvisit
The SP code is
CREATE PROCEDURE aneadm.uspGetAppAllSetting
AS
-- version 1.0
SET NOCOUNT ON
DECLARE @.ReturnCode INTEGER
DECLARE @.IsActiveBit BIT
DECLARE @.Site VARCHAR(80)
DECLARE @.SiteID INTEGER
SELECT @.ReturnCode = 1 -- not ok
SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
DECLARE @.tSettingDesc VARCHAR(255)
SELECT @.tSettingDesc = NULL
SELECT SettingKey,
SettingDesc,
NoOfCopies,
IsPreview
FROM tblAppSetting (nolock)
WHERE SiteID = @.SiteID
AND IsActive = 1
GO
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...[vbcol=seagreen]
> Do you mean that ii is working and i is not? Do you receive any errors?
> Try executing the stored procedure code manually.
> Or post both i and ii code here.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>|||Once you set the application role, user_name() will return 'EMERGE'. Is this
what you want?
Or maybe suser_sname() for the current user id?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:

> For stored procedure, i did not receive any errors but it return me zero
> result. If i didn't set application role and run the SP, it return me 55
> rows of record.
> In the Query Analyzer, the statement i run is
> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
> Step 2 Normal Select Statement) select * from aneadm.tblvisit
> The SP code is
>
> CREATE PROCEDURE aneadm.uspGetAppAllSetting
> AS
> -- version 1.0
> SET NOCOUNT ON
> DECLARE @.ReturnCode INTEGER
> DECLARE @.IsActiveBit BIT
> DECLARE @.Site VARCHAR(80)
> DECLARE @.SiteID INTEGER
> SELECT @.ReturnCode = 1 -- not ok
> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
> DECLARE @.tSettingDesc VARCHAR(255)
> SELECT @.tSettingDesc = NULL
> SELECT SettingKey,
> SettingDesc,
> NoOfCopies,
> IsPreview
> FROM tblAppSetting (nolock)
> WHERE SiteID = @.SiteID
> AND IsActive = 1
> GO
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
>
>|||Thanks.
I think i know why I can't retrieve the records when using Stored Procedure.
Regards
Vanessa
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:2CA7337C-3FCC-4F51-8DCA-8F6F6C70E855@.microsoft.com...[vbcol=seagreen]
> Once you set the application role, user_name() will return 'EMERGE'. Is
> this
> what you want?
> Or maybe suser_sname() for the current user id?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>

Appliction role Problem with Stored Procedure

Hi,
I'm new using Application Role. I use SQL Query Analyzer to try some
example.
I have create a Application role call "ABC" and have set permission to all
tables & stored procedures.
In Query Analyzer, after setting the role using sp_setapprole,
i) I try to run the stored procedure but it give me 0 result. (it
should return me some records which it doesn't)
ii) I try to run a select statement for a table, it return me all
records.
Since Application Role allows me to set permission for Stored Procedures &
tables, I thought both should be the same.
Does anyone know why is this so?
Regards
VanessaDo you mean that ii is working and i is not? Do you receive any errors?
Try executing the stored procedure code manually.
Or post both i and ii code here.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:
> Hi,
> I'm new using Application Role. I use SQL Query Analyzer to try some
> example.
> I have create a Application role call "ABC" and have set permission to all
> tables & stored procedures.
> In Query Analyzer, after setting the role using sp_setapprole,
> i) I try to run the stored procedure but it give me 0 result. (it
> should return me some records which it doesn't)
> ii) I try to run a select statement for a table, it return me all
> records.
> Since Application Role allows me to set permission for Stored Procedures &
> tables, I thought both should be the same.
> Does anyone know why is this so?
> Regards
> Vanessa
>
>|||For stored procedure, i did not receive any errors but it return me zero
result. If i didn't set application role and run the SP, it return me 55
rows of record.
In the Query Analyzer, the statement i run is
Step 1) EXEC sp_setapprole 'EMERGE', 'test'
Step 2 SP ) Exec aneadm.uspGetAppAllSetting
Step 2 Normal Select Statement) select * from aneadm.tblvisit
The SP code is
CREATE PROCEDURE aneadm.uspGetAppAllSetting
AS
-- version 1.0
SET NOCOUNT ON
DECLARE @.ReturnCode INTEGER
DECLARE @.IsActiveBit BIT
DECLARE @.Site VARCHAR(80)
DECLARE @.SiteID INTEGER
SELECT @.ReturnCode = 1 -- not ok
SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
DECLARE @.tSettingDesc VARCHAR(255)
SELECT @.tSettingDesc = NULL
SELECT SettingKey,
SettingDesc,
NoOfCopies,
IsPreview
FROM tblAppSetting (nolock)
WHERE SiteID = @.SiteID
AND IsActive = 1
GO
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
> Do you mean that ii is working and i is not? Do you receive any errors?
> Try executing the stored procedure code manually.
> Or post both i and ii code here.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>> Hi,
>> I'm new using Application Role. I use SQL Query Analyzer to try some
>> example.
>> I have create a Application role call "ABC" and have set permission to
>> all
>> tables & stored procedures.
>> In Query Analyzer, after setting the role using sp_setapprole,
>> i) I try to run the stored procedure but it give me 0 result. (it
>> should return me some records which it doesn't)
>> ii) I try to run a select statement for a table, it return me all
>> records.
>> Since Application Role allows me to set permission for Stored Procedures
>> &
>> tables, I thought both should be the same.
>> Does anyone know why is this so?
>> Regards
>> Vanessa
>>|||Once you set the application role, user_name() will return 'EMERGE'. Is this
what you want?
Or maybe suser_sname() for the current user id?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:
> For stored procedure, i did not receive any errors but it return me zero
> result. If i didn't set application role and run the SP, it return me 55
> rows of record.
> In the Query Analyzer, the statement i run is
> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
> Step 2 Normal Select Statement) select * from aneadm.tblvisit
> The SP code is
>
> CREATE PROCEDURE aneadm.uspGetAppAllSetting
> AS
> -- version 1.0
> SET NOCOUNT ON
> DECLARE @.ReturnCode INTEGER
> DECLARE @.IsActiveBit BIT
> DECLARE @.Site VARCHAR(80)
> DECLARE @.SiteID INTEGER
> SELECT @.ReturnCode = 1 -- not ok
> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
> DECLARE @.tSettingDesc VARCHAR(255)
> SELECT @.tSettingDesc = NULL
> SELECT SettingKey,
> SettingDesc,
> NoOfCopies,
> IsPreview
> FROM tblAppSetting (nolock)
> WHERE SiteID = @.SiteID
> AND IsActive = 1
> GO
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
> >
> > Do you mean that ii is working and i is not? Do you receive any errors?
> >
> > Try executing the stored procedure code manually.
> >
> > Or post both i and ii code here.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Vanessa" wrote:
> >
> >> Hi,
> >>
> >> I'm new using Application Role. I use SQL Query Analyzer to try some
> >> example.
> >>
> >> I have create a Application role call "ABC" and have set permission to
> >> all
> >> tables & stored procedures.
> >>
> >> In Query Analyzer, after setting the role using sp_setapprole,
> >> i) I try to run the stored procedure but it give me 0 result. (it
> >> should return me some records which it doesn't)
> >> ii) I try to run a select statement for a table, it return me all
> >> records.
> >>
> >> Since Application Role allows me to set permission for Stored Procedures
> >> &
> >> tables, I thought both should be the same.
> >>
> >> Does anyone know why is this so?
> >>
> >> Regards
> >> Vanessa
> >>
> >>
> >>
>
>|||Thanks.
I think i know why I can't retrieve the records when using Stored Procedure.
Regards
Vanessa
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:2CA7337C-3FCC-4F51-8DCA-8F6F6C70E855@.microsoft.com...
> Once you set the application role, user_name() will return 'EMERGE'. Is
> this
> what you want?
> Or maybe suser_sname() for the current user id?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>> For stored procedure, i did not receive any errors but it return me zero
>> result. If i didn't set application role and run the SP, it return me 55
>> rows of record.
>> In the Query Analyzer, the statement i run is
>> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
>> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
>> Step 2 Normal Select Statement) select * from aneadm.tblvisit
>> The SP code is
>>
>> CREATE PROCEDURE aneadm.uspGetAppAllSetting
>> AS
>> -- version 1.0
>> SET NOCOUNT ON
>> DECLARE @.ReturnCode INTEGER
>> DECLARE @.IsActiveBit BIT
>> DECLARE @.Site VARCHAR(80)
>> DECLARE @.SiteID INTEGER
>> SELECT @.ReturnCode = 1 -- not ok
>> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
>> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
>> DECLARE @.tSettingDesc VARCHAR(255)
>> SELECT @.tSettingDesc = NULL
>> SELECT SettingKey,
>> SettingDesc,
>> NoOfCopies,
>> IsPreview
>> FROM tblAppSetting (nolock)
>> WHERE SiteID = @.SiteID
>> AND IsActive = 1
>> GO
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
>> >
>> > Do you mean that ii is working and i is not? Do you receive any errors?
>> >
>> > Try executing the stored procedure code manually.
>> >
>> > Or post both i and ii code here.
>> >
>> > Ben Nevarez, MCDBA, OCP
>> > Database Administrator
>> >
>> >
>> > "Vanessa" wrote:
>> >
>> >> Hi,
>> >>
>> >> I'm new using Application Role. I use SQL Query Analyzer to try some
>> >> example.
>> >>
>> >> I have create a Application role call "ABC" and have set permission to
>> >> all
>> >> tables & stored procedures.
>> >>
>> >> In Query Analyzer, after setting the role using sp_setapprole,
>> >> i) I try to run the stored procedure but it give me 0 result.
>> >> (it
>> >> should return me some records which it doesn't)
>> >> ii) I try to run a select statement for a table, it return me
>> >> all
>> >> records.
>> >>
>> >> Since Application Role allows me to set permission for Stored
>> >> Procedures
>> >> &
>> >> tables, I thought both should be the same.
>> >>
>> >> Does anyone know why is this so?
>> >>
>> >> Regards
>> >> Vanessa
>> >>
>> >>
>> >>
>>