sql server tools: roles

Showing posts with label roles. Show all posts

Thursday, March 8, 2012

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

Application roles, good or bad?

We are trying to determine if using application roles would be the best
method to access our SQL database with a "to be developed" ASP.Net
application or possibly an Access project. Some of the requirements of the
application will require different users to have access to different parts
of the database. Some users may be able to modify data while other users
might be read only users. I assume that this would require the application
to use different application roles depending on the user that is logging
into the application?
Another requirement of the application is the ability to maintain an audit
trail for users. So, either we will still have to use the user account to
create the initial connection to the database before applying the
application role or the user name will have to be passed in by the
application so that it can be used for auditing if another (single) account
is used for the initial connection to the database. Are there any guidelines
for best practice or recommended practice? Thanks.
Paul Bauer
paul.bauer@.rimrockgroup.com
www.rimrockgroup.comThere's some good info on role base authentication at the following website;
Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/SecNetch03.asp
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||From what you described it doesn't look like using application roles will
fit your model.
When an application role is activated for a connection by the application,
the connection
permanently loses all permissions applied to the login, user account, or
other groups or
database roles in all databases for the duration of the connection. The
connection gains the
permissions associated with the application role for the database in which
the application role exists.
This means all users who connects the db through this application will have
the same permissions in
this db (unless your implement your own logic inside the application, which
doesn't seems to be your goal).
Using Windows authentification seems to be better solution here.
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclaimer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Paul Bauer" <paul.bauer@.rimrockgroup.com> wrote in message
news:#z$3c0zTEHA.2580@.TK2MSFTNGP12.phx.gbl...
> We are trying to determine if using application roles would be the best
> method to access our SQL database with a "to be developed" ASP.Net
> application or possibly an Access project. Some of the requirements of the
> application will require different users to have access to different parts
> of the database. Some users may be able to modify data while other users
> might be read only users. I assume that this would require the application
> to use different application roles depending on the user that is logging
> into the application?
> Another requirement of the application is the ability to maintain an audit
> trail for users. So, either we will still have to use the user account to
> create the initial connection to the database before applying the
> application role or the user name will have to be passed in by the
> application so that it can be used for auditing if another (single)
account
> is used for the initial connection to the database. Are there any
guidelines
> for best practice or recommended practice? Thanks.
> Paul Bauer
> paul.bauer@.rimrockgroup.com
> www.rimrockgroup.com
>
>

Wednesday, March 7, 2012

Application Roles with IIS

I wish to use SQL Server 2000 Application Security with an ASP (Classic) app
lication.
When I run the ASP app and look at SQL Server Current Activity Process Info
, the column Application is showing "Internet Information Services". How do
I align SQL Server and IIS so that Application Security can be utilised?
regards
Greg
PS I have already set the Application Name for this site in IIS.If you are using ASP to connect to SQL Server then the application is IIS
so that is what is diplayed. What application would you prefer it to
display?
Rand
This posting is provided "as is" with no warranties and confers no rights.

Application roles Licensing

I am trying to verify some information that was given to me by another (rival) admin, I was told by him that you can use the server plus user CAL licensing model and buy only enough CAL's to cover as many application roles you use. Is this correct because
if it is, we spent way too much money on licensing and I think that Microsoft would want to change their licensing policy in these regards. Sorry I didn't know what other Discussion Group to post this question. Thanks for any help on this matter.
"DanielG" <DanielG@.discussions.microsoft.com> wrote in message
news:5F87CAE9-2435-4BFD-96D3-15AC1E9A901B@.microsoft.com...
> I am trying to verify some information that was given to me by another
(rival) admin, I was told by him that you can use the server plus user CAL
licensing model and buy only enough CAL's to cover as many application roles
you use. Is this correct because if it is, we spent way too much money on
licensing and I think that Microsoft would want to change their licensing
policy in these regards. Sorry I didn't know what other Discussion Group to
post this question. Thanks for any help on this matter.
I had not heard of cals based on application roles... There is a licensing
white paper you may want to
check(http://www.microsoft.com/sql/howtobu...rlicensing.asp). While
this does a fair job, it always seems like you need to talk to a lawyer to
get the full story!
Steve
|||Thank you for your reply; I did read the document before I posted (posting is usually the last step I take) but there is no reference to application roles. In theory it does seem possible to just buy a CAL for each application role, I mean logically it is
just one user logging in several times right?
"Steve Thompson" wrote:

> "DanielG" <DanielG@.discussions.microsoft.com> wrote in message
> news:5F87CAE9-2435-4BFD-96D3-15AC1E9A901B@.microsoft.com...
> (rival) admin, I was told by him that you can use the server plus user CAL
> licensing model and buy only enough CAL's to cover as many application roles
> you use. Is this correct because if it is, we spent way too much money on
> licensing and I think that Microsoft would want to change their licensing
> policy in these regards. Sorry I didn't know what other Discussion Group to
> post this question. Thanks for any help on this matter.
> I had not heard of cals based on application roles... There is a licensing
> white paper you may want to
> check(http://www.microsoft.com/sql/howtobu...rlicensing.asp). While
> this does a fair job, it always seems like you need to talk to a lawyer to
> get the full story!
> Steve
>
>
|||I believe your rival is wrong. I'm not sure if CAL's are required for each
user or each system (computer) that accesses the server, but it's definitely
not by application role.
Mike Kruchten
"DanielG" <DanielG@.discussions.microsoft.com> wrote in message
news:5F87CAE9-2435-4BFD-96D3-15AC1E9A901B@.microsoft.com...
> I am trying to verify some information that was given to me by another
(rival) admin, I was told by him that you can use the server plus user CAL
licensing model and buy only enough CAL's to cover as many application roles
you use. Is this correct because if it is, we spent way too much money on
licensing and I think that Microsoft would want to change their licensing
policy in these regards. Sorry I didn't know what other Discussion Group to
post this question. Thanks for any help on this matter.
|||My thought would be you could use CAL licensing if there's a way you can identify who is logging in before the application role takes over. That's where the licensing takes place, not at the role level. If you can't identify who is logging in, a process
or license is required.
"DGroebe" wrote:

> Thank you for your reply; I did read the document before I posted (posting is usually the last step I take) but there is no reference to application roles. In theory it does seem possible to just buy a CAL for each application role, I mean logically it
is just one user logging in several times right?[vbcol=seagreen]
> "Steve Thompson" wrote:

Application roles Licensing

I am trying to verify some information that was given to me by another (rival) admin, I was told by him that you can use the server plus user CAL licensing model and buy only enough CAL's to cover as many application roles you use. Is this correct because if it is, we spent way too much money on licensing and I think that Microsoft would want to change their licensing policy in these regards. Sorry I didn't know what other Discussion Group to post this question. Thanks for any help on this matter."DanielG" <DanielG@.discussions.microsoft.com> wrote in message
news:5F87CAE9-2435-4BFD-96D3-15AC1E9A901B@.microsoft.com...
> I am trying to verify some information that was given to me by another
(rival) admin, I was told by him that you can use the server plus user CAL
licensing model and buy only enough CAL's to cover as many application roles
you use. Is this correct because if it is, we spent way too much money on
licensing and I think that Microsoft would want to change their licensing
policy in these regards. Sorry I didn't know what other Discussion Group to
post this question. Thanks for any help on this matter.
I had not heard of cals based on application roles... There is a licensing
white paper you may want to
check(http://www.microsoft.com/sql/howtobuy/sqlserverlicensing.asp). While
this does a fair job, it always seems like you need to talk to a lawyer to
get the full story!
Steve|||Thank you for your reply; I did read the document before I posted (posting is usually the last step I take) but there is no reference to application roles. In theory it does seem possible to just buy a CAL for each application role, I mean logically it is just one user logging in several times right?
"Steve Thompson" wrote:
> "DanielG" <DanielG@.discussions.microsoft.com> wrote in message
> news:5F87CAE9-2435-4BFD-96D3-15AC1E9A901B@.microsoft.com...
> > I am trying to verify some information that was given to me by another
> (rival) admin, I was told by him that you can use the server plus user CAL
> licensing model and buy only enough CAL's to cover as many application roles
> you use. Is this correct because if it is, we spent way too much money on
> licensing and I think that Microsoft would want to change their licensing
> policy in these regards. Sorry I didn't know what other Discussion Group to
> post this question. Thanks for any help on this matter.
> I had not heard of cals based on application roles... There is a licensing
> white paper you may want to
> check(http://www.microsoft.com/sql/howtobuy/sqlserverlicensing.asp). While
> this does a fair job, it always seems like you need to talk to a lawyer to
> get the full story!
> Steve
>
>|||I believe your rival is wrong. I'm not sure if CAL's are required for each
user or each system (computer) that accesses the server, but it's definitely
not by application role.
Mike Kruchten
"DanielG" <DanielG@.discussions.microsoft.com> wrote in message
news:5F87CAE9-2435-4BFD-96D3-15AC1E9A901B@.microsoft.com...
> I am trying to verify some information that was given to me by another
(rival) admin, I was told by him that you can use the server plus user CAL
licensing model and buy only enough CAL's to cover as many application roles
you use. Is this correct because if it is, we spent way too much money on
licensing and I think that Microsoft would want to change their licensing
policy in these regards. Sorry I didn't know what other Discussion Group to
post this question. Thanks for any help on this matter.|||My thought would be you could use CAL licensing if there's a way you can identify who is logging in before the application role takes over. That's where the licensing takes place, not at the role level. If you can't identify who is logging in, a processor license is required.
"DGroebe" wrote:
> Thank you for your reply; I did read the document before I posted (posting is usually the last step I take) but there is no reference to application roles. In theory it does seem possible to just buy a CAL for each application role, I mean logically it is just one user logging in several times right?
> "Steve Thompson" wrote:
> > "DanielG" <DanielG@.discussions.microsoft.com> wrote in message
> > news:5F87CAE9-2435-4BFD-96D3-15AC1E9A901B@.microsoft.com...
> > > I am trying to verify some information that was given to me by another
> > (rival) admin, I was told by him that you can use the server plus user CAL
> > licensing model and buy only enough CAL's to cover as many application roles
> > you use. Is this correct because if it is, we spent way too much money on
> > licensing and I think that Microsoft would want to change their licensing
> > policy in these regards. Sorry I didn't know what other Discussion Group to
> > post this question. Thanks for any help on this matter.
> >
> > I had not heard of cals based on application roles... There is a licensing
> > white paper you may want to
> > check(http://www.microsoft.com/sql/howtobuy/sqlserverlicensing.asp). While
> > this does a fair job, it always seems like you need to talk to a lawyer to
> > get the full story!
> >
> > Steve
> >
> >
> >

Application roles Licensing

I am trying to verify some information that was given to me by another (riva
l) admin, I was told by him that you can use the server plus user CAL licens
ing model and buy only enough CAL's to cover as many application roles you u
se. Is this correct because
if it is, we spent way too much money on licensing and I think that Microsof
t would want to change their licensing policy in these regards. Sorry I didn
't know what other Discussion Group to post this question. Thanks for any he
lp on this matter."DanielG" <DanielG@.discussions.microsoft.com> wrote in message
news:5F87CAE9-2435-4BFD-96D3-15AC1E9A901B@.microsoft.com...
> I am trying to verify some information that was given to me by another
(rival) admin, I was told by him that you can use the server plus user CAL
licensing model and buy only enough CAL's to cover as many application roles
you use. Is this correct because if it is, we spent way too much money on
licensing and I think that Microsoft would want to change their licensing
policy in these regards. Sorry I didn't know what other Discussion Group to
post this question. Thanks for any help on this matter.
I had not heard of cals based on application roles... There is a licensing
white paper you may want to
check(http://www.microsoft.com/sql/howtob...erlicensing.asp). While
this does a fair job, it always seems like you need to talk to a lawyer to
get the full story!
Steve|||Thank you for your reply; I did read the document before I posted (posting i
s usually the last step I take) but there is no reference to application rol
es. In theory it does seem possible to just buy a CAL for each application r
ole, I mean logically it is
just one user logging in several times right?
"Steve Thompson" wrote:

> "DanielG" <DanielG@.discussions.microsoft.com> wrote in message
> news:5F87CAE9-2435-4BFD-96D3-15AC1E9A901B@.microsoft.com...
> (rival) admin, I was told by him that you can use the server plus user CAL
> licensing model and buy only enough CAL's to cover as many application rol
es
> you use. Is this correct because if it is, we spent way too much money on
> licensing and I think that Microsoft would want to change their licensing
> policy in these regards. Sorry I didn't know what other Discussion Group t
o
> post this question. Thanks for any help on this matter.
> I had not heard of cals based on application roles... There is a licensing
> white paper you may want to
> check(http://www.microsoft.com/sql/howtob...erlicensing.asp). While
> this does a fair job, it always seems like you need to talk to a lawyer to
> get the full story!
> Steve
>
>|||I believe your rival is wrong. I'm not sure if CAL's are required for each
user or each system (computer) that accesses the server, but it's definitely
not by application role.
Mike Kruchten
"DanielG" <DanielG@.discussions.microsoft.com> wrote in message
news:5F87CAE9-2435-4BFD-96D3-15AC1E9A901B@.microsoft.com...
> I am trying to verify some information that was given to me by another
(rival) admin, I was told by him that you can use the server plus user CAL
licensing model and buy only enough CAL's to cover as many application roles
you use. Is this correct because if it is, we spent way too much money on
licensing and I think that Microsoft would want to change their licensing
policy in these regards. Sorry I didn't know what other Discussion Group to
post this question. Thanks for any help on this matter.|||My thought would be you could use CAL licensing if there's a way you can ide
ntify who is logging in before the application role takes over. That's wher
e the licensing takes place, not at the role level. If you can't identify w
ho is logging in, a process
or license is required.
"DGroebe" wrote:

> Thank you for your reply; I did read the document before I posted (posting is usua
lly the last step I take) but there is no reference to application roles. In theory
it does seem possible to just buy a CAL for each application role, I mean logically
it
is just one user logging in several times right?[vbcol=seagreen]
> "Steve Thompson" wrote:
>

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 for Cross-Database Joins

I have an application that segregates data into two different
databases. Database A has stored procs that perform joins between
tables in database A and database B. I am thinking that I have reached
the limits of Application Roles, but correct me if I am wrong.
My application creates a connection to database A as 'testuser' with
read only access, then executes sp_setapprole to gain read write
permissions. Even then the only way 'testuser' can get data out of the
databases is via stored procs or views, no access to tables directly.
Anyone know of a solution? Here is the error I get:

Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationInfo, Line
38
Server user 'testuser' is not a valid user in database 'DatabaseB'

The system user is in fact in database A and B.

thanks

Jason SchaitelJason_Schaitel (jason_schaitel@.hotmail.com) writes:
> I have an application that segregates data into two different
> databases. Database A has stored procs that perform joins between
> tables in database A and database B. I am thinking that I have reached
> the limits of Application Roles, but correct me if I am wrong.
> My application creates a connection to database A as 'testuser' with
> read only access, then executes sp_setapprole to gain read write
> permissions. Even then the only way 'testuser' can get data out of the
> databases is via stored procs or views, no access to tables directly.
> Anyone know of a solution? Here is the error I get:
> Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationInfo, Line
> 38
> Server user 'testuser' is not a valid user in database 'DatabaseB'
> The system user is in fact in database A and B.

Books Online says:

When an application role is activated, the permissions usually
associated with the user's connection that activated the application
role are ignored. The user's connection gains the permissions
associated with the application role for the database in which the
application role is defined. The user's connection can gain access to
another database only through permissions granted to the guest user
account in that database. Therefore, if the guest user account does not
exist in a database, the connection cannot gain access to that
database.

That is, once you have set the application role in A, you are someone
else, and your access outside A is limited.

The one way I can think of to sort this out - beside uniting the databases
into one - is to enable the server configuration parameter "Cross DB
Ownership Chaining". This option was added in SP3 is off by default.
If there no other databases from other applications on the server,
there is no problem to enable this option. However, on consolidated
server that hosts databases for unrelated applications, this is not
recommendable.

For cross DB chaining to work, the databases must also have the same
owner.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>
> The one way I can think of to sort this out - beside uniting the databases
> into one - is to enable the server configuration parameter "Cross DB
> Ownership Chaining". This option was added in SP3 is off by default.
> If there no other databases from other applications on the server,
> there is no problem to enable this option. However, on consolidated
> server that hosts databases for unrelated applications, this is not
> recommendable.

Jason could instead enable the 'db chaining' database option for only those
databases needed by the application rather than turning the cross-database
chaining server-wide.

> For cross DB chaining to work, the databases must also have the same
> owner.

This is true, assuming the objects are owned by 'dbo', because database
ownership determines the dbo user mapping. In the case of non-dbo-owned
objects, the object owners in the different databases need to map to the
same login in order to maintain an unbroken ownership chain.

> The user's connection can gain access to
> another database only through permissions granted to the guest user
> account in that database. Therefore, if the guest user account does not
> exist in a database, the connection cannot gain access to that
> database.

To expand on this BOL excerpt, it's necessary to enable the guest user in
the non-application role databases so that users have a security context
after the application role is enabled. However, no permissions need to be
granted to guest or public in Jason's situation because access is done only
through views and procs from application role database.

--
Hope this helps.

Dan Guzman
SQL Server MVP|||I have tried to look in BOL and Google Groups for the how to enable the
cross database ownership chaining option at the database level and not
having much luck. Can you point me to it?

thanks

Jason|||Jason_Schaitel (jason_schaitel@.hotmail.com) writes:
> I have tried to look in BOL and Google Groups for the how to enable the
> cross database ownership chaining option at the database level and not
> having much luck. Can you point me to it?

exec sp_dboption yourdb, 'db chaining', true

This option is not in the original Books Online, as it was added in SP3.
But it is in the updated Books Online, see link below.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Application Roles ENCRYPT function Valid Password Characters

When I try to use the ODBC canonical ENCRYPT function for SP_SETAPPROLE, I
get an ODBC error when certain otherwise good characters are used in the
password. What characters are and are not allowed for passwords for
application roles while using the ENCRYPT function?You might ask me what is a good password? Here is a sample:
wZ726BcF_vR?goLVmxgsGLkZpqQbZ<Yfu<?L<t
RQzCa85c?"DXtI,QPbUtIyBSJbqF?
(without the line return)
"Chuck Hawkins" <charles.hawkins@.NOSPAMjenzabar.net> wrote in message
news:ebySvpBfFHA.3944@.TK2MSFTNGP10.phx.gbl...
> When I try to use the ODBC canonical ENCRYPT function for SP_SETAPPROLE, I
> get an ODBC error when certain otherwise good characters are used in the
> password. What characters are and are not allowed for passwords for
> application roles while using the ENCRYPT function?
>|||You can find the valid characters in the books online help
topic: Security Rules.
You can find the topic in the index under passwords, rules
for
-Sue
On Tue, 28 Jun 2005 15:49:06 -0400, "Chuck Hawkins"
<charles.hawkins@.NOSPAMjenzabar.net> wrote:

>When I try to use the ODBC canonical ENCRYPT function for SP_SETAPPROLE, I
>get an ODBC error when certain otherwise good characters are used in the
>password. What characters are and are not allowed for passwords for
>application roles while using the ENCRYPT function?
>|||Thank you, Sue. I went back and re-wrote my password generation script to
remove references to the unallowed characters mentioned in Security Rules
for passwords - []{}(),;?*! @..
I'm still having the problem with the ENCRYPT function. I execute:
sp_setapprole
@.rolename = 'TEST',
@.password = {Encrypt N
'ro111гPM0ci?TxmOK
e3qJDtSV?Xrи"S??D
k2?q6L?EvrvI1mOENycWpLvz
jL?3kn'}
--@.password = {Encrypt N 'easy'}
,@.encrypt = 'odbc'
go
And get:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
I know I don't have a syntax error (other than an ugly password). When I
switch the TEST app role over to a password of 'easy', it works.
Am I supposed to put braces [] around the password somehow?
So the question remains, what characters are not allowed for passwords? I
know []{}(),;?*! @.. are not, but I don't have any of these.
Chuck
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:99a4c1p6996f5mklnjbq0hhmapjunkv95u@.
4ax.com...
> You can find the valid characters in the books online help
> topic: Security Rules.
> You can find the topic in the index under passwords, rules
> for
> -Sue
> On Tue, 28 Jun 2005 15:49:06 -0400, "Chuck Hawkins"
> <charles.hawkins@.NOSPAMjenzabar.net> wrote:
>
>|||Incidentally, here is the ugly password generation code:
set nocount on
declare @.counter int,
@.password varchar(128),
@.char char(1),
@.charindex int,
@.loop int
/* Unallowed characters:
! = 33
( = 40
) = 41
, = 40
* = 42
; = 59
? = 63
@. = 64
[ = 91
] = 93
{ = 123
} = 125
*/
select @.counter = 1, @.password = ''
while @.counter < 2
begin
--Restrict the password to 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if (@.charindex between 65 and 90 or @.charindex between 97 and 122)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 4
begin
--Restrict the password to 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if (@.charindex between 48 and 57 or @.charindex between 65 and 90 or
@.charindex between 97 and 122)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 5
begin
--Restrict the password to 0-9
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if @.charindex between 48 and 57 --or @.charindex between 65 and 90 or
@.charindex between 97 and 122
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 10
begin
-- Restrict the password to NOT 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if --@.charindex between 48 and 57 or @.charindex between 65 and 90 or
@.charindex between 97 and 122
--or
(@.charindex between 161 and 255 or @.charindex between 130 AND 140)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 11
begin
--Restrict the password to 0-9
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if @.charindex between 48 and 57 --or @.charindex between 65 and 90 or
@.charindex between 97 and 122
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 129
begin
--Restrict the password to 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if (@.charindex between 48 and 57 or @.charindex between 65 and 90 or
@.charindex between 97 and 122
or @.charindex between 161 and 255 or @.charindex between 130 AND 140)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
select RTRIM(@.password) AS Password
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:99a4c1p6996f5mklnjbq0hhmapjunkv95u@.
4ax.com...
> You can find the valid characters in the books online help
> topic: Security Rules.
> You can find the topic in the index under passwords, rules
> for
> -Sue
> On Tue, 28 Jun 2005 15:49:06 -0400, "Chuck Hawkins"
> <charles.hawkins@.NOSPAMjenzabar.net> wrote:
>
>|||What I've discovered:
The password characters are not allowed for or the canonical ENCRYPT functio
n does not work with characters with the following ASCII codes:
(33,40,41,42,59,63,64,91,93,123,125,130,
132,133,134,135,136,137,139,161,162,
166,167,168,169,171,172,173,174,175,176,
177,180,182,184,187,188,189,190,191,
215,247)
Further, in order for the ENCRYPT function to work, the password cannot be m
ore than 64 characters (vice 128 allowed in Security Rules).
All that said, it still doesn't work. When I enter the following code, I can
not get the SP_SETAPPROLE to work:
exec sp_dropapprole 'TEST_APPROLE'
go
exec sp_addapprole
@.rolename = 'TEST_APPROLE',
@.password = 'rwq4?37ctEPn0izwhJ6dq
dACSZcfmfia?fWG
'
go
sp_setapprole
@.rolename = 'TEST_APPROLE',
@.password = {Encrypt N 'rwq4?37ctEPn0izwhJ6dq
dACSZc
fmfia?fWG'}
--@.password = {Encrypt N 'easy'}
,@.encrypt = 'odbc'
go
Server: Msg 2764, Level 16, State 1, Procedure sp_setapprole, Line 41
Incorrect password supplied for application role 'TEST_APPROLE'.
So the question remains, what are valid password characters for application
roles in order for the ENCRYPT function to work?
And now we have a new question, why does the ENCRYPT function limit you to 6
4 characters? I have my suppositions but I'd love to hear from someone who k
nows.
Chuck Hawkins
"Chuck Hawkins" <charles.hawkins@.NOSPAMjenzabar.net> wrote in message news:OUXAtKKfFHA.572@.T
K2MSFTNGP15.phx.gbl...
> Thank you, Sue. I went back and re-wrote my password generation script to
> remove references to the unallowed characters mentioned in Security Rules
> for passwords - []{}(),;?*! @..
> I'm still having the problem with the ENCRYPT function. I execute:
>
> sp_setapprole
> @.rolename = 'TEST',
> @.password = {Encrypt N
> 'ro111гPM0ci?TxmOK
e3qJDtSV?Xrи"S??
Dk2?q6L?EvrvI1mOENycWpLv
zjL?3kn'}
> --@.password = {Encrypt N 'easy'}
> ,@.encrypt = 'odbc'
> go
>
> And get:
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violatio
n
>
> I know I don't have a syntax error (other than an ugly password). When I
> switch the TEST app role over to a password of 'easy', it works.
>
> Am I supposed to put braces [] around the password somehow?
>
> So the question remains, what characters are not allowed for passwords? I
> know []{}(),;?*! @.. are not, but I don't have any of these.
>
> Chuck
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:99a4c1p6996f5mklnjbq0hhmapjunkv95u@.
4ax.com...
>
>

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 and Bulk Insert

Is it possible to grant a user access to Bulk Insert via an Application Role
?
I have a situation where I dont want to grant users direct access to any
tables, but there is one table that they will need to do a bulk insert into.
I cant see anywhere that you can assign an approle as part of bulkadmin.
Any ideas of how to do this would be appreciated. Thanks.I don't believe you can use "Application roles" for this, but you can assign
"Bulk Insert Administrator" Server Role to any individual Server Login...
Under SQL Server Login Properties, Server Roles, one of the Server Roles is
Bulk insert Administrator.
"Jace" wrote:

> Is it possible to grant a user access to Bulk Insert via an Application Ro
le?
> I have a situation where I dont want to grant users direct access to any
> tables, but there is one table that they will need to do a bulk insert int
o.
> I cant see anywhere that you can assign an approle as part of bulkadmin.
> Any ideas of how to do this would be appreciated. Thanks.

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

Can anybody tell, how to implement the application roles in SQL Server
2000.

Basically, I want to Implement the application roles in our
application, so that it can be application specific. Its' an clients
requirement from we people.

Thanks

Prashant Thakwanithakwani@.rediffmail.com (Prashant Thakwani) wrote in message news:<bf0d42bf.0403032120.588fb947@.posting.google.com>...
> Can anybody tell, how to implement the application roles in SQL Server
> 2000.
> Basically, I want to Implement the application roles in our
> application, so that it can be application specific. Its' an clients
> requirement from we people.
> Thanks
> Prashant Thakwani

1. Create the role with sp_addapprole
2. Grant permissions to the role with GRANT
3. Use sp_setapprole to activate the role - you now have the role's
permissions, not your own permissions
4. Code your application to use sp_setapprole

There are examples for these commands in Books Online - are you having
a specific problem implementing them? If so, perhaps you could give
more information about what commands you're using, what errors or
unexpected behaviour you see etc.

Simon

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 roles

I an having problems setting up an application role:
-- create the app role
exec sp_addapprole 'MyAppRole', ''approlepassword'
-- grant it ALL priv
grant all to MyAppRole
-- create new new user
exec sp_addlogin 'User1', 'password', 'MyDatabase'
use MyDatabase
sp_grantdbaccess 'User1'
-- new user login and executes
exec sp_setapprole 'MyAppRole', ''approlepassword'
now User1 tries to do anyithing and they have no access to any objects, why
is this, I have granted all priv to the app role. I must be missing
something basic here.
Can anyone point me in the right direction.
Thanks,
Tony"Tony" <tonyng2@.spacecommand.net> wrote in message
news:exnIzfn6DHA.2568@.TK2MSFTNGP10.phx.gbl...

quote:

> I an having problems setting up an application role:
> -- create the app role
> exec sp_addapprole 'MyAppRole', ''approlepassword'
> -- grant it ALL priv
> grant all to MyAppRole
> -- create new new user
> exec sp_addlogin 'User1', 'password', 'MyDatabase'
> use MyDatabase
> sp_grantdbaccess 'User1'
> -- new user login and executes
> exec sp_setapprole 'MyAppRole', ''approlepassword'
> now User1 tries to do anyithing and they have no access to any objects,

why

quote:

> is this, I have granted all priv to the app role. I must be missing
> something basic here.
> Can anyone point me in the right direction.
> Thanks,
> Tony
>
>

GRANT ALL does not grant object permissions (SELECT, UPDATE etc.) - it
grants statement permissions (CREATE TABLE, BACKUP LOG etc.). To grant
object permissions, you need to grant individual permissions for each
object:
grant execute on proc1 to MyAppRole
grant update on table1 to MyAppRole
etc.
You can make this easier by using built-in roles like
db_datareader/db_datawriter, or by cutting, pasting, reviewing and executing
the output of a query like this:
select 'grant execute on ' + routine_name + ' to MyAppRole'
from information_schema.routines
where routine_type = 'procedure'
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:401ff029$1_2@.news.bluewin.ch...

quote:

> "Tony" <tonyng2@.spacecommand.net> wrote in message
> news:exnIzfn6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> why
> GRANT ALL does not grant object permissions (SELECT, UPDATE etc.) - it
> grants statement permissions (CREATE TABLE, BACKUP LOG etc.). To grant
> object permissions, you need to grant individual permissions for each
> object:
> grant execute on proc1 to MyAppRole
> grant update on table1 to MyAppRole
> etc.
> You can make this easier by using built-in roles like
> db_datareader/db_datawriter, or by cutting, pasting, reviewing and

executing

quote:

> the output of a query like this:
> select 'grant execute on ' + routine_name + ' to MyAppRole'
> from information_schema.routines
> where routine_type = 'procedure'
> Simon
>

--
But, I dynamically add/remove objects all the time. I surly don't want to
have to reset ll the permissions each time.
I guess using application roles is not going to cut if for me, since this
would be way to much of a maint. headache. Guess I will just have to assign
the users to built in roles unless someone knows an easier way to maintain
the app role when new objects are being added/removed at any time without
requiring the app role to be changed.
Would be really nice if I could grant the app role as another role such as
dbadmin.
Thanks,
Tony|||Okay figure out I can assign app role to be a member of db_owner.
But, when a user is set to the app role, all objects created by this user
get the app role owner, and not dbo as they should if they have db_owner.
Why is this?
Example:
sp_addlogin User1, password, db1
use db1
sp_grantdbaccess User1
sp_addapprole MyAppRole, rolepassword
sp_addrolemember db_owner, MyAppRole
Now user logins in as User1
sp_setapprole MyAppRole, rolepassword
Create table Test(Name varchar(50))
Test table is now owned by MyAppRole as MyAppRole.Test instead of dbo.Test
as is should (well I think it should)
Why is this?
Tony
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:401ff029$1_2@.news.bluewin.ch...

quote:

> "Tony" <tonyng2@.spacecommand.net> wrote in message
> news:exnIzfn6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> why
> GRANT ALL does not grant object permissions (SELECT, UPDATE etc.) - it
> grants statement permissions (CREATE TABLE, BACKUP LOG etc.). To grant
> object permissions, you need to grant individual permissions for each
> object:
> grant execute on proc1 to MyAppRole
> grant update on table1 to MyAppRole
> etc.
> You can make this easier by using built-in roles like
> db_datareader/db_datawriter, or by cutting, pasting, reviewing and

executing

quote:

> the output of a query like this:
> select 'grant execute on ' + routine_name + ' to MyAppRole'
> from information_schema.routines
> where routine_type = 'procedure'
> Simon
>

|||A db_owner role member needs to explicitly specify owner 'dbo' in order to
create dbo-owned objects.
CREATE TABLE dbo.Test(Name varchar(50))
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony" <tonyng2@.spacecommand.net> wrote in message
news:%233fORlv6DHA.2572@.TK2MSFTNGP09.phx.gbl...

quote:

> Okay figure out I can assign app role to be a member of db_owner.
> But, when a user is set to the app role, all objects created by this user
> get the app role owner, and not dbo as they should if they have db_owner.
> Why is this?
> Example:
> sp_addlogin User1, password, db1
> use db1
> sp_grantdbaccess User1
> sp_addapprole MyAppRole, rolepassword
> sp_addrolemember db_owner, MyAppRole
> Now user logins in as User1
> sp_setapprole MyAppRole, rolepassword
> Create table Test(Name varchar(50))
> Test table is now owned by MyAppRole as MyAppRole.Test instead of dbo.Test
> as is should (well I think it should)
> Why is this?
> Tony
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:401ff029$1_2@.news.bluewin.ch...
objects,[QUOTE]
> executing
>

|||Hmm, I 'm used to it doing that automatically if you ARE a db_owner.
But I can handle that.
Thanks,
Tony
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23pkSCPy6DHA.2432@.TK2MSFTNGP10.phx.gbl...

quote:

> A db_owner role member needs to explicitly specify owner 'dbo' in order to
> create dbo-owned objects.
> CREATE TABLE dbo.Test(Name varchar(50))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tony" <tonyng2@.spacecommand.net> wrote in message
> news:%233fORlv6DHA.2572@.TK2MSFTNGP09.phx.gbl...
user[QUOTE]
db_owner.[QUOTE]
dbo.Test[QUOTE]
> objects,
>

Application Role and Securityadmin

Helo,
I've got an application wich uses application roles... The problem is that
some of this users must add and remove users from the SQL Server. Since the
application role overrides the user settings I need to find a way for the
user to abandon the application role in order to gran or deny database
access, as well as adding or removing user logins from the SQL Server.
I have not found a way to abandon the application role in order to execute
this commands... or a way wich I could execute this commands without leaving
the application role.
Any one has a solution for this "problem"?
Thank you in advance.Juan
Just a guess
Perhaps you need to create a second app role with an appropriate permissions
and within the appliaction to check out to which of app role to set up.
"Juan" <ssccrriipptteerr@.tteerrrraa.eess> wrote in message
news:erFNliNwEHA.1976@.TK2MSFTNGP09.phx.gbl...
> Helo,
> I've got an application wich uses application roles... The problem is that
> some of this users must add and remove users from the SQL Server. Since
the
> application role overrides the user settings I need to find a way for the
> user to abandon the application role in order to gran or deny database
> access, as well as adding or removing user logins from the SQL Server.
> I have not found a way to abandon the application role in order to execute
> this commands... or a way wich I could execute this commands without
leaving
> the application role.
> Any one has a solution for this "problem"?
> Thank you in advance.
>|||I've been thinking about this a couple of days while reimplementing the
application...
If I use an application role I loose all the user privileges, therefore I'm
not part of the securityadministrators, therefore I can't add logins to my
server, neither I can grant database access. I need this for some of my
users (Finally I made this users a user role, and left all others as
Application roles).
As well, you can't grant the application Role security admin privileges,
since its not a session login on the server, and it's specific to a
database...
I guess I'll have to use my changes in the application (Application roles
for everyone except those who need the ability to add users)...
"Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
news:#lmA68YwEHA.1524@.TK2MSFTNGP09.phx.gbl...
> Juan
> Just a guess
> Perhaps you need to create a second app role with an appropriate
permissions
> and within the appliaction to check out to which of app role to set up.
>
>
> "Juan" <ssccrriipptteerr@.tteerrrraa.eess> wrote in message
> news:erFNliNwEHA.1976@.TK2MSFTNGP09.phx.gbl...
that[vbcol=seagreen]
> the
the[vbcol=seagreen]
execute[vbcol=seagreen]
> leaving
>|||Hi Juan,
Since the application role is only actived via application, you may still
let your users using the application role when they are using the
application, but make separate SQL connections using their own SQL login
accounts to add users/grant DB access.
Thanks,
Lan Lewis-Bevan
MS SQL support
This posting is provided "AS IS" with no warranties, and confers no rights.

Application Role & 2nd Database

I'm trying to use application roles for the 1st time and have a problem.
Most of the select statements I use reference tables in a separate database.
After reading the BOL I find that this only works through the GUEST account.
Drat!
Any suggestions to fix this? I'm considering setting up views in the local
DB, but this will involve changing the code and creating the views and I
still don't know if it will work.
--
Jeffrey R. Price
Database Manager
Computing & Communication Services
Max M. Fisher college of Business
The Ohio State University
320F Mason Hall
250 W. Woodruff Avenue
Columbus, OH 43210-1309Creating referencing views will work fine. If you don't want to grant
permissions on the underlying tables to guest or public, you can use an
unbroken cross-database ownership chain so that the app role needs only
permissions on views in the application role database.
To maintain an unbroken chain, the owners of the objects involved need to
map to the same login. If your objects are owned by 'dbo', this
necessitates that the owners of both databases be the same. Also, the
cross-database chaining option (introduced in SQL 2000 SP3) needs to be
enabled for those databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use application roles for the 1st time and have a problem.
> Most of the select statements I use reference tables in a separate
database.
> After reading the BOL I find that this only works through the GUEST
account.
> Drat!
> Any suggestions to fix this? I'm considering setting up views in the
local
> DB, but this will involve changing the code and creating the views and I
> still don't know if it will work.
> --
> Jeffrey R. Price
> Database Manager
> Computing & Communication Services
> Max M. Fisher college of Business
> The Ohio State University
> 320F Mason Hall
> 250 W. Woodruff Avenue
> Columbus, OH 43210-1309
>|||Thanks, I can run it fine now from Query Analyzer, but not my app... more
work to do there.
I had to set the DB owners as the same account and a few other tasks. The
following MSDN article helped:
http://msdn.microsoft.com/library/d.../>
up_1cj5.asp
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ecqxs2D8DHA.2056@.TK2MSFTNGP10.phx.gbl...
> Creating referencing views will work fine. If you don't want to grant
> permissions on the underlying tables to guest or public, you can use an
> unbroken cross-database ownership chain so that the app role needs only
> permissions on views in the application role database.
> To maintain an unbroken chain, the owners of the objects involved need to
> map to the same login. If your objects are owned by 'dbo', this
> necessitates that the owners of both databases be the same. Also, the
> cross-database chaining option (introduced in SQL 2000 SP3) needs to be
> enabled for those databases.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> database.
> account.
> local
>|||Correction......
I did not get the Application role to work across Databases. I continue to
receive the error "Server user 'price_9' is not a valid user in database
'FCoB_Contacts'."
I've tried both
EXEC sp_configure 'Cross DB Ownership Chaining', '1';RECONFIGURE
and
EXEC sp_configure 'Cross DB Ownership Chaining', '0';RECONFIGURE with
EXEC sp_dboption 'FCoB_Contacts', 'db chaining', 'TRUE'
Both DBs are owned by the same account.
Drat!
"Jeff Price" <price.9@.osu.edu> wrote in message
news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use application roles for the 1st time and have a problem.
> Most of the select statements I use reference tables in a separate
database.
> After reading the BOL I find that this only works through the GUEST
account.
> Drat!
> Any suggestions to fix this? I'm considering setting up views in the
local
> DB, but this will involve changing the code and creating the views and I
> still don't know if it will work.
> --
> Jeffrey R. Price
> Database Manager
> Computing & Communication Services
> Max M. Fisher college of Business
> The Ohio State University
> 320F Mason Hall
> 250 W. Woodruff Avenue
> Columbus, OH 43210-1309
>|||Since an application role is only known in a single database, you need to
enable the 'guest' user in the other database so you have a security context
in the other database. No guest user permissions need to be granted. For
example:
Use MyOtherDatabase
EXEC sp_adduser 'guest'
Also, you don't need to enable cross-database chaining at the server level.
Your can specify it at the database level for only the databases that need
this option turned on:
EXEC sp_dboption 'MyDatabase, 'db chaining', 'TRUE'
EXEC sp_dboption 'MyOtherDatabase, 'db chaining', 'TRUE'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:epWnGnO8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> Correction......
> I did not get the Application role to work across Databases. I continue
to
> receive the error "Server user 'price_9' is not a valid user in database
> 'FCoB_Contacts'."
> I've tried both
> EXEC sp_configure 'Cross DB Ownership Chaining', '1';RECONFIGURE
> and
> EXEC sp_configure 'Cross DB Ownership Chaining', '0';RECONFIGURE with
> EXEC sp_dboption 'FCoB_Contacts', 'db chaining', 'TRUE'
> Both DBs are owned by the same account.
> Drat!
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> database.
> account.
> local
>|||Thanks! That did the trick.
Do we need to be concerned with the existence of the "Guest" account?
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e4vgGmQ8DHA.4060@.tk2msftngp13.phx.gbl...
> Since an application role is only known in a single database, you need to
> enable the 'guest' user in the other database so you have a security
context
> in the other database. No guest user permissions need to be granted. For
> example:
> Use MyOtherDatabase
> EXEC sp_adduser 'guest'
> Also, you don't need to enable cross-database chaining at the server
level.
> Your can specify it at the database level for only the databases that need
> this option turned on:
> EXEC sp_dboption 'MyDatabase, 'db chaining', 'TRUE'
> EXEC sp_dboption 'MyOtherDatabase, 'db chaining', 'TRUE'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:epWnGnO8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> to
problem.
I
>|||> Do we need to be concerned with the existence of the "Guest" account?
As long as you haven't granted additional permissions to guest or public,
guest user access is limited to default public role permissions. This
includes the ability to view meta data, (e.g. table aned column names) but
no access to user objects and data. Users will still need an account to
connect to SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:OkTtG8j8DHA.2404@.TK2MSFTNGP12.phx.gbl...
> Thanks! That did the trick.
> Do we need to be concerned with the existence of the "Guest" account?
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:e4vgGmQ8DHA.4060@.tk2msftngp13.phx.gbl...
to
> context
For
> level.
need
continue
database
with
> problem.
the
and
> I
>

Thursday, March 8, 2012

Applications ,Roles and users

Applications ,Roles and users

Applications ,Roles and users

Application roles, good or bad?

Wednesday, March 7, 2012

Application Roles with IIS

Application roles Licensing

Application roles Licensing

Application roles Licensing

Application Roles in SQL 2005

Application Roles for Cross-Database Joins

Application Roles ENCRYPT function Valid Password Characters

application roles and strong passwords

Application Roles and Bulk Insert

Application Roles across databases in SQL Server 2000

Application roles

Application Roles

Application Roles

Application roles

Application Role and Securityadmin

Application Role & 2nd Database

sql server tools

Blog Archive

About Me