Apply permissions to user, role or group?

Hi guys,

I'd appreciate your thoughts on this. Not done too much DB admin. Usually I do development work and the admins to the admin.

The database is behind an API of stored procedures to manipulate the data, and views to select the data.

The database needs to be accessed remotely by multiple clients.

How best to keep the database secure?

Create a new user and login on the database which is made known to all client applications. Then grant execute permission on the stored procs and grant select on the views?

There is probably a better way than one login for all? Should I be looking at roles and groups etc? If so, how best to set that up?

A few pointers would be gratefully received!

What are you trying to protect and from whom? Who should have access to what? What kinds of access do you want to allow? You should start by asking yourself such questions and once you gather the answers, you can start designing your application security to enforce these access restrictions.

There is no best database security model - if you don't have anything to protect, you won't need a security model at all. Best is relative to the needs of a specific application.

If you tell us what you are trying to obtain, we'll try to help you get it.

Thanks
Laurentiu

|||

Thanks Laurentiu,

The database contains billing information and server will be (in some cases) visible on the Internet. Users manage the data using client application software. It is this software that uses the stored proc API.

I created a specific login known to the software and proceeded to grant execute permission to the API on this login.

The API is quite extensive and while doing this I wondered if there was a better way, perhaps using roles or whatever, so that I can grant permissions to the API once and then allow different logins, including SSPI logins to be members of that role, or group or whatever it should be.

Looking for pointers and advice on the best direction to go on this.

|||

I still need more details about your users: are they having diferent roles, which would require different access restrictions to your data? If they all have the same level of access, do you need to distinguish between them, would you want to know, for example, who did what operation and have an auditing system?

There are many ways to achieve security, and it is hard to tell what is the best solution for you. Here are some possibilities, but I can't really recommend one without knowing more about what you are trying to do.

1) You can have all users connecting to your application with the same credentials, and the software will connect to the server as some login. This won't allow you to know who does what, it will only restrict access to those that know how to connect to the application.

2) You can have users connecting to your application with distinct credentials. You would manage these credentials within your application. The connections to the server would be done using the same login, but your application can implement custom auditing because it does the user authentication. Also, any access rights will have to be controlled at the application layer.

3) You can have users connecting again with distinct credentials, but in this case the credentials correspond to SQL Server logins, and for each user, you connect to SQL Server using the corresponding login. You can do auditing in this case either at the application level or at the server level, within the stored procedures that you call. You can manage rights granted to users at SQL Server level and you can use roles for easier management.

If you don't know exactly what you will want to do, it is a good idea to keep your options open. So, use roles and grant permissions on roles; then, if you need to have those permissions accessible to more than one user, you can just add them to the role.

Hope this helps.

Laurentiu

|||

Laurentiu

Been playing around with it and option 3 seems to suit us best. Keeps it flexible, for example, it allows us to split the API into different roles.

Thanks for your ideas!

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

Applications ,Roles and users

Situation :
- via an application "xApp" a user has role "xRole" on a Db
- Via an application "yApp" the same user has an other "yRole" on the same Db
- We use WinAuthentication ( Ad Groups liked to Role in the Db )
- The Application we can't change ( not owned by us )
Question :
Can we in any way assign a role ( change a role ) when the user connects ? This based on the application used. maybe via the connect string ?
any suggestion would be welcome.
PeterThere is no trigger or event under which you can place code when a user logs
in or changes databases, which is what you would need...
Since roles are fixed, you'll have to grant both roles to the user... If you
chould change the apps, you could use an application role, but since you
can't change the application it is not an option...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Situation :
> - via an application "xApp" a user has role "xRole" on a Db
> - Via an application "yApp" the same user has an other "yRole" on the same
Db
> - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> - The Application we can't change ( not owned by us )
> Question :
> Can we in any way assign a role ( change a role ) when the user connects ?
This based on the application used. maybe via the connect string ?
> any suggestion would be welcome.
> Peter
>|||Do you know this is forseen in sql2005 ? This would be very usefull for us.
"Wayne Snyder" wrote:
> There is no trigger or event under which you can place code when a user logs
> in or changes databases, which is what you would need...
> Since roles are fixed, you'll have to grant both roles to the user... If you
> chould change the apps, you could use an application role, but since you
> can't change the application it is not an option...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> > Situation :
> > - via an application "xApp" a user has role "xRole" on a Db
> > - Via an application "yApp" the same user has an other "yRole" on the same
> Db
> > - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> > - The Application we can't change ( not owned by us )
> > Question :
> > Can we in any way assign a role ( change a role ) when the user connects ?
> This based on the application used. maybe via the connect string ?
> > any suggestion would be welcome.
> > Peter
> >
>
>|||I do not know, perhaps one of the others has more information...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F0ADB77-480F-4C31-A64A-043481E8B76E@.microsoft.com...
> Do you know this is forseen in sql2005 ? This would be very usefull for
us.
>
> "Wayne Snyder" wrote:
> > There is no trigger or event under which you can place code when a user
logs
> > in or changes databases, which is what you would need...
> >
> > Since roles are fixed, you'll have to grant both roles to the user... If
you
> > chould change the apps, you could use an application role, but since you
> > can't change the application it is not an option...
> >
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Peter" <Peter@.discussions.microsoft.com> wrote in message
> > news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> > > Situation :
> > > - via an application "xApp" a user has role "xRole" on a Db
> > > - Via an application "yApp" the same user has an other "yRole" on the
same
> > Db
> > > - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> > > - The Application we can't change ( not owned by us )
> > > Question :
> > > Can we in any way assign a role ( change a role ) when the user
connects ?
> > This based on the application used. maybe via the connect string ?
> > > any suggestion would be welcome.
> > > Peter
> > >
> >
> >
> >

Applications ,Roles and users

Situation :
- via an application "xApp" a user has role "xRole" on a Db
- Via an application "yApp" the same user has an other "yRole" on the same Db
- We use WinAuthentication ( Ad Groups liked to Role in the Db )
- The Application we can't change ( not owned by us )
Question :
Can we in any way assign a role ( change a role ) when the user connects ? This based on the application used. maybe via the connect string ?
any suggestion would be welcome.
Peter
There is no trigger or event under which you can place code when a user logs
in or changes databases, which is what you would need...
Since roles are fixed, you'll have to grant both roles to the user... If you
chould change the apps, you could use an application role, but since you
can't change the application it is not an option...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Situation :
> - via an application "xApp" a user has role "xRole" on a Db
> - Via an application "yApp" the same user has an other "yRole" on the same
Db
> - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> - The Application we can't change ( not owned by us )
> Question :
> Can we in any way assign a role ( change a role ) when the user connects ?
This based on the application used. maybe via the connect string ?
> any suggestion would be welcome.
> Peter
>
|||Do you know this is forseen in sql2005 ? This would be very usefull for us.
"Wayne Snyder" wrote:

> There is no trigger or event under which you can place code when a user logs
> in or changes databases, which is what you would need...
> Since roles are fixed, you'll have to grant both roles to the user... If you
> chould change the apps, you could use an application role, but since you
> can't change the application it is not an option...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Db
> This based on the application used. maybe via the connect string ?
>
>
|||I do not know, perhaps one of the others has more information...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F0ADB77-480F-4C31-A64A-043481E8B76E@.microsoft.com...
> Do you know this is forseen in sql2005 ? This would be very usefull for
us.[vbcol=seagreen]
>
> "Wayne Snyder" wrote:
logs[vbcol=seagreen]
you[vbcol=seagreen]
same[vbcol=seagreen]
connects ?[vbcol=seagreen]

Applications ,Roles and users

Situation :
- via an application "xApp" a user has role "xRole" on a Db
- Via an application "yApp" the same user has an other "yRole" on the same D
b
- We use WinAuthentication ( Ad Groups liked to Role in the Db )
- The Application we can't change ( not owned by us )
Question :
Can we in any way assign a role ( change a role ) when the user connects ? T
his based on the application used. maybe via the connect string ?
any suggestion would be welcome.
PeterThere is no trigger or event under which you can place code when a user logs
in or changes databases, which is what you would need...
Since roles are fixed, you'll have to grant both roles to the user... If you
chould change the apps, you could use an application role, but since you
can't change the application it is not an option...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Situation :
> - via an application "xApp" a user has role "xRole" on a Db
> - Via an application "yApp" the same user has an other "yRole" on the same
Db
> - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> - The Application we can't change ( not owned by us )
> Question :
> Can we in any way assign a role ( change a role ) when the user connects ?
This based on the application used. maybe via the connect string ?
> any suggestion would be welcome.
> Peter
>|||Do you know this is forseen in sql2005 ? This would be very usefull for us.
"Wayne Snyder" wrote:

> There is no trigger or event under which you can place code when a user lo
gs
> in or changes databases, which is what you would need...
> Since roles are fixed, you'll have to grant both roles to the user... If y
ou
> chould change the apps, you could use an application role, but since you
> can't change the application it is not an option...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Db
> This based on the application used. maybe via the connect string ?
>
>|||I do not know, perhaps one of the others has more information...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F0ADB77-480F-4C31-A64A-043481E8B76E@.microsoft.com...
> Do you know this is forseen in sql2005 ? This would be very usefull for
us.[vbcol=seagreen]
>
> "Wayne Snyder" wrote:
>
logs[vbcol=seagreen]
you[vbcol=seagreen]
same[vbcol=seagreen]
connects ?[vbcol=seagreen]

application_role

I am using SQL Server 2005 Enterprise Edition. I am a little confused on
setting up a Application role. Where do I make users a member of the role?
Is there a good explaination of this somewhere."Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23$WW46i%23HHA.600@.TK2MSFTNGP05.phx.gbl...
>I am using SQL Server 2005 Enterprise Edition. I am a little confused on
>setting up a Application role. Where do I make users a member of the role?
>Is there a good explaination of this somewhere.
>
You don't add users to app roles. App roles are intended as a way of
identifying an application rather than a user.
http://msdn2.microsoft.com/en-us/library/ms190998.aspx
--
David Portas|||Users don't belong to application roles. The application connects to the
database then executes the sp_setapprole procedure to get permissions.
Check this link out...
http://msdn2.microsoft.com/en-us/library/ms190998.aspx
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23$WW46i%23HHA.600@.TK2MSFTNGP05.phx.gbl...
>I am using SQL Server 2005 Enterprise Edition. I am a little confused on
>setting up a Application role. Where do I make users a member of the role?
>Is there a good explaination of this somewhere.
>|||In SQLS 2005 there are better alternatives, which are explained in
Erland Sommarskog's excellent paper, Giving Permissions through Stored
Procedures, http://www.sommarskog.se/grantperm.html.
-mary
On Tue, 18 Sep 2007 14:59:18 -0500, "Tom Reis" <reistom@.cdnet.cod.edu>
wrote:
>I am using SQL Server 2005 Enterprise Edition. I am a little confused on
>setting up a Application role. Where do I make users a member of the role?
>Is there a good explaination of this somewhere.
>

Application Roles in SQL 2005

We have an an application that was written using OLE DB (ADO) against a SQL 2000 Server that uses an Application role to give rights to the database objects. It connects, calls sp_setapprole and goes on. If the database needs to LOCK a record, it is creating a new ADO Connection and instantiating the Approle again. This model has been working fine up til now.

Now we are installing a SQL 2005 server for the latest version of the product we are working on and are running into an error. The error is

Error: 18059, Severity: 20, State: 1.
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.

It's happening when the second ADO Connection for locking a record is being created and the sp_setapprole is being executed.

One of my questions is what is the problem with executing the approle on a different connection? Our code has not changed, so obviously SQL 2005 is doing something different. The other is What can we do to correct this?

Is the resource pooling different? We had problems in the beginning with approles and figured out through research that we needed to add OLE DB Services=-2 to the connection string to turn off resource pooling.

Is there an extra step to using Approles in SQL 2005?

Any help would be greatly appreciated as we need to resolve this ASAP.

Thanks,

David

SQL Server 2005 supports a richer impersonation model, and we made some changes to prevent potential for undesired escalation of privileges on both the existing and new impersonation mechanisms, including approles.

From your description I can guess that you are using a connection pool, and apparently the pool is misusing an impersonated context (approle). Does it still fail if you disable the connection pool?

If this is the case, one solution for using approles and connection pool would be to use the new @.fCreateCookie and @.cookie parameters in sp_setapprole along with sp_unsetapprole (http://msdn2.microsoft.com/en-us/library/ms365415.aspx). This allows the client code to obtain a server-generated cookie that can be used to revert to the original context before resetting the connection.

Unfortunately I haven’t experienced any behavior like the one you described; at this point I cannot really tell if the behavior you are experiencing is a problem on the way the client code is calling SQL Server, if this is a scenario where we decided to change the behavior of SQL Server to prevent an undesired escalation of privileges or a real bug in SQL Server 2005.

Please, let us know if the workaround I suggested work, if not I would like to understand more about the scenario so I can try to reproduce this behavior on my test environment.

Thanks a lot,

-Raul Garcia
SDE/T

SQL Server Engine

|||

We don't need to revert to the original context, so It's supposed to be disabling the resource pooling by adding "OLE DB Services=-2" on the connection string of the ADOConnection. We found the problem with connection pooling coding against SQL 2000.

Is that still effective in a SQL 2005 environment? Has the setting changed? It seems like something isn't set right since this worked in SQL 2000 and doesn't in SQL 2005.

Thanks,

David

|||

To clarify this situation more....

We aren't using .NET and the SqlClient for this app.

We are running a Win32 application, connecting to the SQL Server using ADO (MDAC 2.7 or 2.8), holding this connection open until the application closes at which point we close the connection. We are turning on ROW Level locking in SQL 2000 (not sure if this has been done or not on the SQL 2005 server) and using a separate connection with "locking hints" on the tables when a lock call is made to force a model we have to use for backwards compatability. (i.e.

select * from authors as a WITH (NOLOCK) { normal get }

select * from authors as a with (UPDLOCK) where identitycol = 1 { get with a lock for updating }

)

I wasn't sure how SQL Server 2005 would handle connectivity from the SQL 2000 client with sp3.

|||

Ok. Found the problem.

In certain circumstances, the connection string was being set with the OLE DB Services=-2 like it was supposed to be. Therefore the Pooling was still on and causing the problem.

Thanks for the assistance!

application roles and strong passwords

Hi,
we are testing upgrading an application to run SQL 2005. One issue we
are having is that the one application role they have has a 'weak'
password. We are trying to change the password and it seem to be
inforcing the 'strong password policy from windows. I cannot see a way
around this. Do all application roles need strong passwords? Is there
any way around it?Yes...they should all be strong passwords. Using strong
passwords would be the first option.
If for some reason you can't and need time until you can use
strong passwords, One way around it is to use the old stored
procedures which are only there for backwards compatibility.
sp_addapprole, sp_approlepassword, etc.
-Sue
On 16 Apr 2007 18:15:02 -0700, rjvanzanten
<rvanzant@.premierbankcard.com> wrote:

>Hi,
>we are testing upgrading an application to run SQL 2005. One issue we
>are having is that the one application role they have has a 'weak'
>password. We are trying to change the password and it seem to be
>inforcing the 'strong password policy from windows. I cannot see a way
>around this. Do all application roles need strong passwords? Is there
>any way around it?|||Thanks Sue, I know that a better password would be preferred. But its
a difficult option for us.
Thanks for the tip on the sp_addapprole. It should get us through
this.
Ron
On Apr 16, 11:26 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
> Yes...they should all be strong passwords. Using strong
> passwords would be the first option.
> If for some reason you can't and need time until you can use
> strong passwords, One way around it is to use the old stored
> procedures which are only there for backwards compatibility.
> sp_addapprole, sp_approlepassword, etc.
> -Sue
> On 16 Apr 2007 18:15:02 -0700, rjvanzanten
>
> <rvanz...@.premierbankcard.com> wrote:
> - Show quoted text -|||Sue Hoegemeier (Sue_H@.nomail.please) writes:
> Yes...they should all be strong passwords. Using strong
> passwords would be the first option.
> If for some reason you can't and need time until you can use
> strong passwords, One way around it is to use the old stored
> procedures which are only there for backwards compatibility.
> sp_addapprole, sp_approlepassword, etc.
sp_addrole uses CREATE APPLICATION ROLE, so that wouldn't be any different.
I am not able to run sp_helptext on sp_approlepassword, but I would not
expect it be possible to use password that does not pass the rules.
The only way out would be to modify the Windows policy as the password
for the application role is changed, and then change back.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yeah...I see that in the stored proc but I'm pretty sure the
password check is bypassed - thought it was on one of the
blogs but only found this reference:
http://forums.microsoft.com/MSDN/Sh...576174&SiteID=1
I'm not on a box where I can test it, not until tomorrow.
-Sue
On Tue, 17 Apr 2007 22:10:41 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Sue Hoegemeier (Sue_H@.nomail.please) writes:
>sp_addrole uses CREATE APPLICATION ROLE, so that wouldn't be any different.
>I am not able to run sp_helptext on sp_approlepassword, but I would not
>expect it be possible to use password that does not pass the rules.
>The only way out would be to modify the Windows policy as the password
>for the application role is changed, and then change back.|||Sue Hoegemeier (Sue_H@.nomail.please) writes:
> Yeah...I see that in the stored proc but I'm pretty sure the
> password check is bypassed - thought it was on one of the
> blogs but only found this reference:
> http://forums.microsoft.com/MSDN/Sh...576174&SiteID=1
> I'm not on a box where I can test it, not until tomorrow.
Indeed. I tested it at a server at work, and the password check did
not catch my lousy password. Hm, I wonder how they do that...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yeah...I thought I remember testing while back when I first
read it - I still can't find where I originally read it. I
had not looked at the stored proc though - which does make
it look odd that it works.
-Sue
On Thu, 19 Apr 2007 22:15:18 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Sue Hoegemeier (Sue_H@.nomail.please) writes:
>Indeed. I tested it at a server at work, and the password check did
>not catch my lousy password. Hm, I wonder how they do that...

Application Roles across databases in SQL Server 2000

Hello
I have 2 databases that run application role security
(different role names and passwords), users access these
databases only from within different Visual Basic
applications.
I require to be able to request data from both
databases. I have read in SQL Server help that if you
enable the guest user account and then give it the
relevant permissions the system will only allow the other
database to get to these objects.
I have created a stored procedure on one of the databases
that calls a table in the database with the guest account
enabled. I have not given the guest account access to
this table but I can still get to the data in the table.
Please can someone explain why this is and what I need to
do to prevent this.
Thank you
Caroline> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
This is due to ownership chaining behavior. As long as all objects are
owned by the same login, permissions are not checked on indirectly
referenced objects. Additionally, you need to enable cross database
chaining for ownership chains to apply to cross-database access. This
appears to be the case in your environment.
As long as you access data only via views and procedures, you don't need to
grant any permissions to guest. This allows you to leverage ownership
chains as a security mechanism. See Ownership Chains in the Books Online
for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Caroline" <anonymous@.discussions.microsoft.com> wrote in message
news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
> Hello
> I have 2 databases that run application role security
> (different role names and passwords), users access these
> databases only from within different Visual Basic
> applications.
> I require to be able to request data from both
> databases. I have read in SQL Server help that if you
> enable the guest user account and then give it the
> relevant permissions the system will only allow the other
> database to get to these objects.
> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
> Thank you
> Caroline|||> and what I need to do to prevent this.
Only grant execute permissions on the procedure to those users/roles whom
you want to access the underlying data.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23fcOSPDYEHA.3596@.tk2msftngp13.phx.gbl...
> This is due to ownership chaining behavior. As long as all objects are
> owned by the same login, permissions are not checked on indirectly
> referenced objects. Additionally, you need to enable cross database
> chaining for ownership chains to apply to cross-database access. This
> appears to be the case in your environment.
> As long as you access data only via views and procedures, you don't need
to
> grant any permissions to guest. This allows you to leverage ownership
> chains as a security mechanism. See Ownership Chains in the Books Online
> for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Caroline" <anonymous@.discussions.microsoft.com> wrote in message
> news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
>

Application Roles

Hi
I'm using an application role to restrict acess to the users to a Database
The Application role is always activated without any problem.
Nevertheless I'm receiving permission error messages ("XXXX permission denie
d
on object 'TABLE', database...") when inserting, updating or deleting
records if I open a recordset before this operations, otherwise everything
works fine.
Below is an example of this,
If I call the following code before I trie to INSERT a record in TABLE2 I
receive the permission error message
..
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
..
If I don't call the previous code the INSERT works ...
(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 and
TABLE2)
Do someone have any idea why this behavior ?
I'll apreciate any help.
Many Thanks
Daniel
EXAMPLE:
--
Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Set cnnConn = New ADODB.Connection
With cnnConn
.Open _
"Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=DBx;Data Source=SERVERx"
End With
'The AppRole is activated without problems--
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password} ,
'odbc'"
.CommandType = adCmdText
.Execute
End With
'IF this code is not called the INSERT bellow works fine, otherwise not--
--
--
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
'INSERT record on TABLE2--
With cmdCommand
.CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
('A', 'XXX', 'P')"
.CommandType = adCmdText
.Execute
End With
..
ErrorHandler:
' clean up
End SubDid you turn pooling off in your connection string? If not, then
another connection is being opened under the covers and in that
connection the approle is not active. There's more information at
http://support.microsoft.com/defaul...;en-us;Q229564.
--Mary
On Tue, 05 Sep 2006 13:42:54 GMT, "Daniel Rodrigues" <u26179@.uwe>
wrote:

>Hi
>I'm using an application role to restrict acess to the users to a Database
>The Application role is always activated without any problem.
>Nevertheless I'm receiving permission error messages ("XXXX permission deni
ed
>on object 'TABLE', database...") when inserting, updating or deleting
>records if I open a recordset before this operations, otherwise everything
>works fine.
>Below is an example of this,
>If I call the following code before I trie to INSERT a record in TABLE2 I
>receive the permission error message
>..
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>..
>If I don't call the previous code the INSERT works ...
>(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 an
d
>TABLE2)
>Do someone have any idea why this behavior ?
>I'll apreciate any help.
>Many Thanks
>Daniel
>EXAMPLE:
>--
>Private Sub CommandButton1_Click()
>On Error GoTo ErrorHandler
>Dim cnnConn As ADODB.Connection
>Dim rstRecordset As ADODB.Recordset
>Dim cmdCommand As ADODB.Command
>Set cnnConn = New ADODB.Connection
>With cnnConn
> .Open _
> "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
> "Persist Security Info=False;" & _
> "Initial Catalog=DBx;Data Source=SERVERx"
>End With
>'The AppRole is activated without problems--
>Set cmdCommand = New ADODB.Command
>Set cmdCommand.ActiveConnection = cnnConn
>With cmdCommand
> .CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password}
,
>'odbc'"
> .CommandType = adCmdText
> .Execute
>End With
>'IF this code is not called the INSERT bellow works fine, otherwise not--
--
>--
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>'INSERT record on TABLE2--
>With cmdCommand
> .CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
>('A', 'XXX', 'P')"
> .CommandType = adCmdText
> .Execute
>End With
>..
>ErrorHandler:
> ' clean up
>End Sub|||Hello Mary
Yes.
I used in connection string "OLE DB Services = -2" and I also tried with
"Pooling=’False " (despite I think this is only for .Net and I'm tried wit
h
VB and Delphi6 with the same results.
I already read the KB article you mentioned.
Is there any other way to deactivate pooling ?
The only way I found to solve the problem was with one connection for the
selects and another one only for INSERT, UPDATE and DELETE statments.
By the way, i did't mentioned in my previous mail, I'm using SQLServer 2K
with SP4 and acessing with ADO using Delphi6 aplications.
Thanks for your answer
Best regards
Daniel
Mary Chipman [MSFT] wrote:[vbcol=seagreen]
>Did you turn pooling off in your connection string? If not, then
>another connection is being opened under the covers and in that
>connection the approle is not active. There's more information at
>http://support.microsoft.com/defaul...;en-us;Q229564.
>--Mary
>
>[quoted text clipped - 68 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1

Application Roles

Hi
I'm using an application role to restrict acess to the users to a Database
The Application role is always activated without any problem.
Nevertheless I'm receiving permission error messages ("XXXX permission denied
on object 'TABLE', database...") when inserting, updating or deleting
records if I open a recordset before this operations, otherwise everything
works fine.
Below is an example of this,
If I call the following code before I trie to INSERT a record in TABLE2 I
receive the permission error message
..
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
..
If I don't call the previous code the INSERT works ...
(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 and
TABLE2)
Do someone have any idea why this behavior ?
I'll apreciate any help.
Many Thanks
Daniel
EXAMPLE:
--
Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Set cnnConn = New ADODB.Connection
With cnnConn
.Open _
"Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=DBx;Data Source=SERVERx"
End With
'The AppRole is activated without problems--
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password} ,
'odbc'"
.CommandType = adCmdText
.Execute
End With
'IF this code is not called the INSERT bellow works fine, otherwise not--
--
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
'INSERT record on TABLE2--
With cmdCommand
.CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
('A', 'XXX', 'P')"
.CommandType = adCmdText
.Execute
End With
..
ErrorHandler:
' clean up
End SubDid you turn pooling off in your connection string? If not, then
another connection is being opened under the covers and in that
connection the approle is not active. There's more information at
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q229564.
--Mary
On Tue, 05 Sep 2006 13:42:54 GMT, "Daniel Rodrigues" <u26179@.uwe>
wrote:
>Hi
>I'm using an application role to restrict acess to the users to a Database
>The Application role is always activated without any problem.
>Nevertheless I'm receiving permission error messages ("XXXX permission denied
>on object 'TABLE', database...") when inserting, updating or deleting
>records if I open a recordset before this operations, otherwise everything
>works fine.
>Below is an example of this,
>If I call the following code before I trie to INSERT a record in TABLE2 I
>receive the permission error message
>..
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>..
>If I don't call the previous code the INSERT works ...
>(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 and
>TABLE2)
>Do someone have any idea why this behavior ?
>I'll apreciate any help.
>Many Thanks
>Daniel
>EXAMPLE:
>--
>Private Sub CommandButton1_Click()
>On Error GoTo ErrorHandler
>Dim cnnConn As ADODB.Connection
>Dim rstRecordset As ADODB.Recordset
>Dim cmdCommand As ADODB.Command
>Set cnnConn = New ADODB.Connection
>With cnnConn
> .Open _
> "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
> "Persist Security Info=False;" & _
> "Initial Catalog=DBx;Data Source=SERVERx"
>End With
>'The AppRole is activated without problems--
>Set cmdCommand = New ADODB.Command
>Set cmdCommand.ActiveConnection = cnnConn
>With cmdCommand
> .CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password} ,
>'odbc'"
> .CommandType = adCmdText
> .Execute
>End With
>'IF this code is not called the INSERT bellow works fine, otherwise not--
>--
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>'INSERT record on TABLE2--
>With cmdCommand
> .CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
>('A', 'XXX', 'P')"
> .CommandType = adCmdText
> .Execute
>End With
>..
>ErrorHandler:
> ' clean up
>End Sub|||Hello Mary
Yes.
I used in connection string "OLE DB Services = -2" and I also tried with
"Pooling=â'False " (despite I think this is only for .Net and I'm tried with
VB and Delphi6 with the same results.
I already read the KB article you mentioned.
Is there any other way to deactivate pooling ?
The only way I found to solve the problem was with one connection for the
selects and another one only for INSERT, UPDATE and DELETE statments.
By the way, i did't mentioned in my previous mail, I'm using SQLServer 2K
with SP4 and acessing with ADO using Delphi6 aplications.
Thanks for your answer
Best regards
Daniel
Mary Chipman [MSFT] wrote:
>Did you turn pooling off in your connection string? If not, then
>another connection is being opened under the covers and in that
>connection the approle is not active. There's more information at
>http://support.microsoft.com/default.aspx?scid=kb;en-us;Q229564.
>--Mary
>>Hi
>[quoted text clipped - 68 lines]
>> ' clean up
>>End Sub
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1

Application Role with VB6

In VB6 I've got the following code when opening a connection to sql server:
With cn
.ConnectionString = "MyConnectionString"
.ConnectionTimeout = 10
.Properties("OLE DB SERVICES") = -2
.Open
'Had to allow execute for sps against the windows group until we can
suss app role
.Execute "EXEC sp_setapprole 'MyApp',{ENCRYPT N 'MyPassword'},'ODBC'"
End With
In sql server I've added a windows group to the database to allow the users
of the application to open the connection. I gave the windows group no
permissions of any kind. I added an application role and gave it select,
insert etc. permissions on the tables and execute permissions on all stored
procedures
When the application runs under a windows user (who is in the windows group
I added to the databse), the application can select data from the database
but gets 'execute permission denied' on any stored procedure that it tries t
o
run.
If I examine the permissions of one of these stored procedures, there is a
grant on it for the application role (there are no denies on it at all). If
I
then explicitly grant permission on one of these stored procedures to the
windows group containing the windows user, they are able to access it.
Ok, I think, it looks like it is ignoring the application role, so I deleted
the application role from the database and amended the VB code, the user is
then unable to access any data.
My conclusion then becomes the application role was giving the user access
to table data, but not to stored procedures, and when I granted permission t
o
the windows group this somehow overrode the application role. This is clearl
y
rubbish, because as I understand it, once an application role takes over, no
other permissions matter.
Anybody see what I'm doing wrong?
Thanks
PaulPlease ignore - accidentally posted twice
"Paul Whittaker" wrote:

> In VB6 I've got the following code when opening a connection to sql server
:
> With cn
> .ConnectionString = "MyConnectionString"
> .ConnectionTimeout = 10
> .Properties("OLE DB SERVICES") = -2
> .Open
> 'Had to allow execute for sps against the windows group until we c
an
> suss app role
> .Execute "EXEC sp_setapprole 'MyApp',{ENCRYPT N 'MyPassword'}
,'ODBC'"
> End With
> In sql server I've added a windows group to the database to allow the user
s
> of the application to open the connection. I gave the windows group no
> permissions of any kind. I added an application role and gave it select,
> insert etc. permissions on the tables and execute permissions on all store
d
> procedures
> When the application runs under a windows user (who is in the windows grou
p
> I added to the databse), the application can select data from the database
> but gets 'execute permission denied' on any stored procedure that it tries
to
> run.
> If I examine the permissions of one of these stored procedures, there is a
> grant on it for the application role (there are no denies on it at all). I
f I
> then explicitly grant permission on one of these stored procedures to the
> windows group containing the windows user, they are able to access it.
> Ok, I think, it looks like it is ignoring the application role, so I delet
ed
> the application role from the database and amended the VB code, the user i
s
> then unable to access any data.
> My conclusion then becomes the application role was giving the user access
> to table data, but not to stored procedures, and when I granted permission
to
> the windows group this somehow overrode the application role. This is clea
rly
> rubbish, because as I understand it, once an application role takes over,
no
> other permissions matter.
> Anybody see what I'm doing wrong?
> Thanks
> Paul

Application Role with vb

I use Application role with vb.
I connect to sql server by windows authentication.
I setup each login to public role of database.
In Database , i add application role and set permission via application
role.
When VB App connect to database , it use windows authentication.
Before It connectd , i call stored procedure (sp_setapprole) to database.
It work fine except that if i use boundcolumn to control in form.
it's occure error for consequent code try to select data from table.
It warn that "permission denied".
Thanks in advanceIt's not work yet.
But i close recordset of bound column, it can select data but bound column
can not see data.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:eH6bONNeDHA.1832@.TK2MSFTNGP09.phx.gbl...
> It may be that connection pooling is resetting the connection so you
> lose the app role security context. Assuming you're using SQLOLEDB, try
> disabling connection pooling by including ';OLE DB Services=-2' in your
> connection string. For example:
> Provider=SQLOLEDB;Data Source=MyServer;Integrated Security=SSPI;Persist
> Security Info=False;;OLE DB Services=-2
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "ton" <barameek@.inglife.co.th> wrote in message
> news:e5ydwCNeDHA.3204@.TK2MSFTNGP11.phx.gbl...
> > I use Application role with vb.
> > I connect to sql server by windows authentication.
> > I setup each login to public role of database.
> > In Database , i add application role and set permission via
> application
> > role.
> > When VB App connect to database , it use windows authentication.
> > Before It connectd , i call stored procedure (sp_setapprole) to
> database.
> > It work fine except that if i use boundcolumn to control in form.
> > it's occure error for consequent code try to select data from table.
> > It warn that "permission denied".
> >
> > Thanks in advance
> >
> >
> >
>

Application Role VB6

In VB6 I have the following code to use an application role
With cn
.ConnectionString = "MyConnectionString"
.ConnectionTimeout = 10
.Properties("OLE DB SERVICES") = -2
.Open
'Had to allow execute for sps against the windows group until we can
suss app role
.Execute "EXEC sp_setapprole 'MyApp',{ENCRYPT N 'MyPassword'},'ODBC'"
End With
I added a windows group to my sql server database, containing windows users
who will use this app. I gave the group no permissions of any kind. This
group as I understand it will allow the users to open the initial connection
to the database before the application role is then applied to the connectio
n.
Next I added an application role to the database and gave it select, insert
etc. on any tables the app needs and execute permission on any stored
procedures the app uses.
When a user (from the windows group) runs the app, data can be selected from
the tables but you get 'Execute permission denied' on any stored procedure
that runs.
If I explicitly grant permission to the windows group for one of the stored
procedures, they are able to access the stored procedure from within the app
.
Ok, I think it looks like the app isn't using the application role.
If I delete the application role from the database and amend the VB code so
as not to use an application role, the user is unable to perform the select
operation on any table, this confuses me as it looks like it was using the
application role to gain access to the data.
Anybody got any ideas.
Regards
PaulYou might try running a Profiler trace to see what's going on behind the
scenes. I suspect ADO is opening a separate connection for your stored proc
execution and this fails because the app role wasn't activated on that
connection.
Note that ADO will implicitly open another connection when the specified
connection is busy, such as in the case of an open recordset on that
connection.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Whittaker" <PaulWhittaker@.discussions.microsoft.com> wrote in message
news:DEBB1148-23EF-425B-92D6-55DE6CA4DD77@.microsoft.com...
> In VB6 I have the following code to use an application role
> With cn
> .ConnectionString = "MyConnectionString"
> .ConnectionTimeout = 10
> .Properties("OLE DB SERVICES") = -2
> .Open
> 'Had to allow execute for sps against the windows group until we
> can
> suss app role
> .Execute "EXEC sp_setapprole 'MyApp',{ENCRYPT N
> 'MyPassword'},'ODBC'"
> End With
> I added a windows group to my sql server database, containing windows
> users
> who will use this app. I gave the group no permissions of any kind. This
> group as I understand it will allow the users to open the initial
> connection
> to the database before the application role is then applied to the
> connection.
> Next I added an application role to the database and gave it select,
> insert
> etc. on any tables the app needs and execute permission on any stored
> procedures the app uses.
> When a user (from the windows group) runs the app, data can be selected
> from
> the tables but you get 'Execute permission denied' on any stored procedure
> that runs.
> If I explicitly grant permission to the windows group for one of the
> stored
> procedures, they are able to access the stored procedure from within the
> app.
> Ok, I think it looks like the app isn't using the application role.
> If I delete the application role from the database and amend the VB code
> so
> as not to use an application role, the user is unable to perform the
> select
> operation on any table, this confuses me as it looks like it was using the
> application role to gain access to the data.
> Anybody got any ideas.
> Regards
> Paul|||Thanks Dan
That was spot on, it was opening another connection (a recordset hadn't been
closed before it tried to execute the stored procedure).
Paul
"Dan Guzman" wrote:

> You might try running a Profiler trace to see what's going on behind the
> scenes. I suspect ADO is opening a separate connection for your stored pr
oc
> execution and this fails because the app role wasn't activated on that
> connection.
> Note that ADO will implicitly open another connection when the specified
> connection is busy, such as in the case of an open recordset on that
> connection.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul Whittaker" <PaulWhittaker@.discussions.microsoft.com> wrote in messag
e
> news:DEBB1148-23EF-425B-92D6-55DE6CA4DD77@.microsoft.com...
>
>

Application Role Usage

I am new to the security part of SQL server and I'm having a problem with
using the application role while running a query from Excel. I wrote the sp
I'm using and it works fine but I can't get the application role to work. I
have set it up in EM but I'm not sure how to put it in the code in Microsoft
query. Do I have to put the exec sp_setapprole 'role', 'password' statement
in the sp or before I execute the procedure. I tried putting it before and i
t
did not work, maybe I had the wrong syntax or something. Below is what I
wrote:
Exec sp_setapprole 'role', 'password'
GO
Exec GetDefectReport
GOThis should work I think...do you get an error message?
Did you give the approle execute permission on the SP (maybe underlying
tables?)
Does the sp return data for Excel to display (I take it, that's what it has
to do...)?
Lee-Z
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:4C25B4B8-FEC4-49C4-9276-F5B0AF2A6FD1@.microsoft.com...
>I am new to the security part of SQL server and I'm having a problem with
> using the application role while running a query from Excel. I wrote the
> sp
> I'm using and it works fine but I can't get the application role to work.
> I
> have set it up in EM but I'm not sure how to put it in the code in
> Microsoft
> query. Do I have to put the exec sp_setapprole 'role', 'password'
> statement
> in the sp or before I execute the procedure. I tried putting it before and
> it
> did not work, maybe I had the wrong syntax or something. Below is what I
> wrote:
> Exec sp_setapprole 'role', 'password'
> GO
> Exec GetDefectReport
> GO|||The error message is that the syntax is wrong around GO. I wrote the sp in
the query analyzer and now i am calling it from Excel using Microsoft Query.
"Lee-Z" wrote:

> This should work I think...do you get an error message?
> Did you give the approle execute permission on the SP (maybe underlying
> tables?)
> Does the sp return data for Excel to display (I take it, that's what it ha
s
> to do...)?
> Lee-Z
>
> "A.B." <AB@.discussions.microsoft.com> wrote in message
> news:4C25B4B8-FEC4-49C4-9276-F5B0AF2A6FD1@.microsoft.com...
>
>|||have never tried approle with MS Query, but try to execute the sp_SetAppRole
statement in menu "File"-> "Execute SQL" from MS-Query (leave out the GO
part). Make sure you select your database in the dropdown box.
After that you should be able to get data from your original query (leave
out the "GO" here as well)...
good luck
Lee-Z
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:1FDE810C-6F0F-4E7C-8537-E5987C88D297@.microsoft.com...[vbcol=seagreen]
> The error message is that the syntax is wrong around GO. I wrote the sp in
> the query analyzer and now i am calling it from Excel using Microsoft
> Query.
> "Lee-Z" wrote:
>|||You are not going to be able to use Microsoft Query and get
application roles to perform reliably. Application roles are
connection-specific, and the tools open additonal connections under
the covers in order to speed up connections. This is an issue even if
you code data access in ADO code (which is the usual way to go about
it). The following article, "SQL application role errors with OLE DB
resource pooling" describes the problem and the workaround:
http://support.microsoft.com/defaul...b;en-us;Q229564
--Mary
On Fri, 12 Aug 2005 06:59:20 -0700, "A.B."
<AB@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The error message is that the syntax is wrong around GO. I wrote the sp in
>the query analyzer and now i am calling it from Excel using Microsoft Query
.
>"Lee-Z" wrote:
>|||Thanks for the help Lee and Mary
"Mary Chipman [MSFT]" wrote:

> You are not going to be able to use Microsoft Query and get
> application roles to perform reliably. Application roles are
> connection-specific, and the tools open additonal connections under
> the covers in order to speed up connections. This is an issue even if
> you code data access in ADO code (which is the usual way to go about
> it). The following article, "SQL application role errors with OLE DB
> resource pooling" describes the problem and the workaround:
> http://support.microsoft.com/defaul...b;en-us;Q229564
> --Mary
> On Fri, 12 Aug 2005 06:59:20 -0700, "A.B."
> <AB@.discussions.microsoft.com> wrote:
>
>

Application role to access xp_cmdshell

I have an Access app linked to a SQL server db. This app uses
xp_cmdshell in some stored procedures and it works fine. As long as
the user is administrator... I'd like to set up an application role
that can execute xp_cmdshell and access my db but I don't know how to
do it as xp_cmdshell is in the Master db while everything else is in
my own db. I'm also unsure whether to call sp_setapprole from the sp's
or from the Access app.
Can somebody please give me some code examples or direct me to a good
site?
/CarlAs long as the ownership chain is unbroken, direct permissions on
xp_cmdshell are not needed. This necessitates that your user procs be owned
by 'dbo', your user database be owned by 'sa' and cross-database chaining
(intoduced in SQL 2000 SP3) be enabled. Example script below.
For security reasons, it is important that your user proc be coded in such a
way that only the intended command can be executed. Also, you should enable
cross-database chaining only if you fully trust users that have permissions
to create dbo-owned objects. See Cross-database chaining in the SQL 2000
Books Online for more information.
You will also need to allow non-sysadmin users to execute xp_cmdshell. You
can do this from Enterprise Manager under Management-->SQL Server
Agent-->Job System. Uncheck the 'Only users with sysadmin privileges...'
check box and specify the Windows account you want to use as the OS security
context for non-sysadmin users. This account should have the minimal
permissions need to perform the needed tasks.

> I'm also unsure whether to call sp_setapprole from the sp's
> or from the Access app.
You'll need to execute sp_setapprole directly from your application. From
the Books Online:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_sp_sa-sz_6tt1.htm">
The sp_setapprole stored procedure can be executed only by direct
Transact-SQL statements; it cannot be executed within another stored
procedure or from within a user-defined transaction.
</Excerpt>
USE MyDatabase
EXEC sp_changedbowner 'sa'
GO
-- for SQL 2000 SP3+
EXEC sp_dboption 'MyDatabase', 'db chaining', true
GO
CREATE PROC dbo.MyXpCmdShellProc
AS
EXEC master..xp_cmdshell 'MyCommand'
GO
GRANT EXEC ON dbo.MyXpCmdShellProc TO MyAppRole
GO
EXEC sp_setapprole 'MyAppRole', 'MyAppRolePassword'
EXEC dbo.MyXpCmdShellProc
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Carl Olsson" <caos@.regerar.com> wrote in message
news:d495b147.0402170439.d8b1453@.posting.google.com...
> I have an Access app linked to a SQL server db. This app uses
> xp_cmdshell in some stored procedures and it works fine. As long as
> the user is administrator... I'd like to set up an application role
> that can execute xp_cmdshell and access my db but I don't know how to
> do it as xp_cmdshell is in the Master db while everything else is in
> my own db. I'm also unsure whether to call sp_setapprole from the sp's
> or from the Access app.
> Can somebody please give me some code examples or direct me to a good
> site?
> /Carl|||Thanks Dan for your excellent explanation. But unfortunately I'm still
at SQL 7... Any other options?
Carl
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<#kQj98V9DHA.3176@.TK
2MSFTNGP11.phx.gbl>...
> As long as the ownership chain is unbroken, direct permissions on
> xp_cmdshell are not needed. This necessitates that your user procs be own
ed
> by 'dbo', your user database be owned by 'sa' and cross-database chaining
> (intoduced in SQL 2000 SP3) be enabled. Example script below.
>|||The technique will work with SQL 7 too. The only difference is that
cross-database chaining is not configurable under SQL 7 and pre-SQL2000 SP3
(it is always on). Just remember to run the 'db chaining' option on in your
user database if you later upgrade to SQL 2000 SP3+.
Hope this helps.
Dan Guzman
SQL Server MVP
"Carl Olsson" <caos@.regerar.com> wrote in message
news:d495b147.0402172316.6a6cf8b@.posting.google.com...
> Thanks Dan for your excellent explanation. But unfortunately I'm still
> at SQL 7... Any other options?
> Carl
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<#kQj98V9DHA.3176@.TK2MSFTNGP11.phx.gbl>...
owned
chaining

Application Role status

Is there a way to determine if an existing connection has invoked a specific
application role? I need to know so that I don't re-invoke it and get an
error.Hi
If you are doing this within your application then why don't you set a
boolean (class attribute) ?
If you try to set the application role a second time you will get an error
return code and Msg 2762
John
"Stefano Nicolini" wrote:

> Is there a way to determine if an existing connection has invoked a specif
ic
> application role? I need to know so that I don't re-invoke it and get an
> error.
>
>|||Yes, you can check this using the USER_NAME() function. The following
T-SQL activates the approle on the connection if it is not already
activated:
IF (SELECT USER_NAME()) <> 'ApproleName'
EXEC sp_setapprole 'ApproleName', 'password'
--Mary
On Mon, 18 Apr 2005 15:12:27 -0400, "Stefano Nicolini"
<StefanoN@.infotronics.com> wrote:

>Is there a way to determine if an existing connection has invoked a specifi
c
>application role? I need to know so that I don't re-invoke it and get an
>error.
>

application role same as this user

we have many homegrown applications. for each application, we create a
sql user id for the application. that id has db_datareader and
db_datawriter (no other permissions) in its database and cannot access
any other databases. is that basically the same thing as an
application role? if not, what advantages does an application role have
over that?With or without application roles, users still need a login (individual or
shared) to connect to SQL Server. The main advantage of an application role
compared to a regular database role is that you can grant full permissions
to the app role and choose to enable it only from within your application.
This way, users cannot perform ad-hoc queries outside the application unless
they have been granted permissions to do so.
Hope this helps.
Dan Guzman
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:4051B3AC.816C10DF@.dontemailme.com...
> we have many homegrown applications. for each application, we create a
> sql user id for the application. that id has db_datareader and
> db_datawriter (no other permissions) in its database and cannot access
> any other databases. is that basically the same thing as an
> application role? if not, what advantages does an application role have
> over that?
>

Application Role permissions report

Can I generate the report for each application role with their objects &
their permissions using sql stored procedure ?
Let me know.
Thanks for all the help.
Manoj
Answered your question in another thread.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:1FF02616-9454-47DC-951D-14586999778F@.microsoft.com...
> Can I generate the report for each application role with their objects &
> their permissions using sql stored procedure ?
> Let me know.
> Thanks for all the help.
> Manoj