Application Role behaves differently in 2005?

Here is a very simple test that I ran on my 2000 and 2005 box and in
both cases I got different results. When I ran this command on 2000 it
worked fine but when I ran the same script on a 2005 box I got the
follwoing error.
sp_addapprole 'aaa','aa1'
exec sp_setapprole 'aaa','aa1'
exec sp_helpuser UserA
ERROR:
Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
The name supplied (UserA) is not a user, role, or aliased login.
I ran this script on a user database, where UserA has access to the DB.
Can anyone tell me why this same script works fine in 2000 and not in
2005? What do I need to do to get it to work in 2005?
ThanksIt looks like the user (UserA) is not a login in the SQL 2005 server. Check
the server logins.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"shub" <shubtech@.gmail.com> wrote in message
news:1155743364.308040.173700@.74g2000cwt.googlegroups.com...
> Here is a very simple test that I ran on my 2000 and 2005 box and in
> both cases I got different results. When I ran this command on 2000 it
> worked fine but when I ran the same script on a 2005 box I got the
> follwoing error.
> sp_addapprole 'aaa','aa1'
> exec sp_setapprole 'aaa','aa1'
> exec sp_helpuser UserA
> ERROR:
> Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> The name supplied (UserA) is not a user, role, or aliased login.
> I ran this script on a user database, where UserA has access to the DB.
> Can anyone tell me why this same script works fine in 2000 and not in
> 2005? What do I need to do to get it to work in 2005?
> Thanks
>|||Hi
This was already logged at
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125814
The difference is a tightening up of security.
If you did sp_helpuser 'aaa' you would get information.
John
"shub" wrote:
> Here is a very simple test that I ran on my 2000 and 2005 box and in
> both cases I got different results. When I ran this command on 2000 it
> worked fine but when I ran the same script on a 2005 box I got the
> follwoing error.
> sp_addapprole 'aaa','aa1'
> exec sp_setapprole 'aaa','aa1'
> exec sp_helpuser UserA
> ERROR:
> Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> The name supplied (UserA) is not a user, role, or aliased login.
> I ran this script on a user database, where UserA has access to the DB.
> Can anyone tell me why this same script works fine in 2000 and not in
> 2005? What do I need to do to get it to work in 2005?
> Thanks
>|||Thanks John for your response. Its good to know that this issue was
acknowledged by Microsoft.
However I am still not sure what the work around is, how can I get the
sp_helpuser info for UserA
login. UserA has access to the server and the database.
John Bell wrote:
> Hi
> This was already logged at
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125814
> The difference is a tightening up of security.
> If you did sp_helpuser 'aaa' you would get information.
> John
> "shub" wrote:
> > Here is a very simple test that I ran on my 2000 and 2005 box and in
> > both cases I got different results. When I ran this command on 2000 it
> > worked fine but when I ran the same script on a 2005 box I got the
> > follwoing error.
> >
> > sp_addapprole 'aaa','aa1'
> > exec sp_setapprole 'aaa','aa1'
> > exec sp_helpuser UserA
> >
> > ERROR:
> > Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> > The name supplied (UserA) is not a user, role, or aliased login.
> >
> > I ran this script on a user database, where UserA has access to the DB.
> >
> > Can anyone tell me why this same script works fine in 2000 and not in
> > 2005? What do I need to do to get it to work in 2005?
> >
> > Thanks
> >
> >|||Hi
Why are you wanting information about other users when you have set the
application role? If you want information about the original user then you
could gather that before setting the application role.
John
"shub" wrote:
> Thanks John for your response. Its good to know that this issue was
> acknowledged by Microsoft.
> However I am still not sure what the work around is, how can I get the
> sp_helpuser info for UserA
> login. UserA has access to the server and the database.
> John Bell wrote:
> > Hi
> >
> > This was already logged at
> > http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125814
> >
> > The difference is a tightening up of security.
> > If you did sp_helpuser 'aaa' you would get information.
> >
> > John
> >
> > "shub" wrote:
> >
> > > Here is a very simple test that I ran on my 2000 and 2005 box and in
> > > both cases I got different results. When I ran this command on 2000 it
> > > worked fine but when I ran the same script on a 2005 box I got the
> > > follwoing error.
> > >
> > > sp_addapprole 'aaa','aa1'
> > > exec sp_setapprole 'aaa','aa1'
> > > exec sp_helpuser UserA
> > >
> > > ERROR:
> > > Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> > > The name supplied (UserA) is not a user, role, or aliased login.
> > >
> > > I ran this script on a user database, where UserA has access to the DB.
> > >
> > > Can anyone tell me why this same script works fine in 2000 and not in
> > > 2005? What do I need to do to get it to work in 2005?
> > >
> > > Thanks
> > >
> > >
>|||John
The way the application works is after it enables the Application role,
it still tries to figure out what user should get access to what screen
within the application. In order to do that the application runs the
sp_helpuser for the login to detremine what roles the login belongs to,
depending on that it gives access to different screens in the
application.
I did find a work around that seems to work but I am not sure if that
is the best way. If I add the db users and all the db roles to the
secureables of the application role and grant "control", the
sp_helpuser seems to work fine then. But is this the right way to do
it?
Thanks
John Bell wrote:
> Hi
> Why are you wanting information about other users when you have set the
> application role? If you want information about the original user then you
> could gather that before setting the application role.
> John
> "shub" wrote:
> > Thanks John for your response. Its good to know that this issue was
> > acknowledged by Microsoft.
> > However I am still not sure what the work around is, how can I get the
> > sp_helpuser info for UserA
> > login. UserA has access to the server and the database.
> > John Bell wrote:
> > > Hi
> > >
> > > This was already logged at
> > > http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125814
> > >
> > > The difference is a tightening up of security.
> > > If you did sp_helpuser 'aaa' you would get information.
> > >
> > > John
> > >
> > > "shub" wrote:
> > >
> > > > Here is a very simple test that I ran on my 2000 and 2005 box and in
> > > > both cases I got different results. When I ran this command on 2000 it
> > > > worked fine but when I ran the same script on a 2005 box I got the
> > > > follwoing error.
> > > >
> > > > sp_addapprole 'aaa','aa1'
> > > > exec sp_setapprole 'aaa','aa1'
> > > > exec sp_helpuser UserA
> > > >
> > > > ERROR:
> > > > Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> > > > The name supplied (UserA) is not a user, role, or aliased login.
> > > >
> > > > I ran this script on a user database, where UserA has access to the DB.
> > > >
> > > > Can anyone tell me why this same script works fine in 2000 and not in
> > > > 2005? What do I need to do to get it to work in 2005?
> > > >
> > > > Thanks
> > > >
> > > >
> >
> >|||Hi
I am not sure why you can't get the information before you set the
application role, most applications I know have user permissions stored in
their own tables and are not dependent on the roles, therefore all you would
need to know is the userid (such as SYSTEM_USER).
Granting too many privileges to anyone is not a good idea, I think adding
the application role to the db_securityadmin or db_accessadmin database roles
would give it privileges to get all user information back, but you don't
necessarily want to do that.
John
"shub" wrote:
> John
> The way the application works is after it enables the Application role,
> it still tries to figure out what user should get access to what screen
> within the application. In order to do that the application runs the
> sp_helpuser for the login to detremine what roles the login belongs to,
> depending on that it gives access to different screens in the
> application.
> I did find a work around that seems to work but I am not sure if that
> is the best way. If I add the db users and all the db roles to the
> secureables of the application role and grant "control", the
> sp_helpuser seems to work fine then. But is this the right way to do
> it?
> Thanks
> John Bell wrote:
> > Hi
> >
> > Why are you wanting information about other users when you have set the
> > application role? If you want information about the original user then you
> > could gather that before setting the application role.
> >
> > John
> >
> > "shub" wrote:
> >
> > > Thanks John for your response. Its good to know that this issue was
> > > acknowledged by Microsoft.
> > > However I am still not sure what the work around is, how can I get the
> > > sp_helpuser info for UserA
> > > login. UserA has access to the server and the database.
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > This was already logged at
> > > > http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125814
> > > >
> > > > The difference is a tightening up of security.
> > > > If you did sp_helpuser 'aaa' you would get information.
> > > >
> > > > John
> > > >
> > > > "shub" wrote:
> > > >
> > > > > Here is a very simple test that I ran on my 2000 and 2005 box and in
> > > > > both cases I got different results. When I ran this command on 2000 it
> > > > > worked fine but when I ran the same script on a 2005 box I got the
> > > > > follwoing error.
> > > > >
> > > > > sp_addapprole 'aaa','aa1'
> > > > > exec sp_setapprole 'aaa','aa1'
> > > > > exec sp_helpuser UserA
> > > > >
> > > > > ERROR:
> > > > > Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 177
> > > > > The name supplied (UserA) is not a user, role, or aliased login.
> > > > >
> > > > > I ran this script on a user database, where UserA has access to the DB.
> > > > >
> > > > > Can anyone tell me why this same script works fine in 2000 and not in
> > > > > 2005? What do I need to do to get it to work in 2005?
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > >
> > >
>