Archival logic/algoritm

We are in need of finalizing an archival approach for one of our Web and Client server application. The major requirements are



a) User can click on Web front end to start archival process.

b) The system should move the related data to archived space in a backup location.

c) Could be a batch process.

d) The relation between tables is extensive i.e. > 30 tables need to be managed for archival one component.

e) Database size is not very huge < 10 MB.





We were planning to have a table to store archival flag, which will be set when user click on Archival. Then a batch program will copy the database in to a backup location and delete the entries from archived database where archive flag is not set and delete entry from master database where archive flag is set. The problem is how to synchronize the changes when archival process runs next time i.e. the master database would have changed so how to put that data in archival database with out removing existing data.



Any other approach/practical solutions will be very helpful.





Regards,

Mridul MishraWant to reread that and tell us if you think that makes sense?

Archive what? How do you know what to mark?

Maybe a simple explination:

"I build widgets...and when a widget is shipped I need to archive the order..."

What are you doing?|||Hmmm...Sorry for being not clear enough. Some how i thought every one is bugged with the problem as much as i do...;)

Well archival is more an event based task...Let me try to give more relevant information here..it's normal microsoft project plan which are stored and displayed for users. In most of the cases users might decide on their own whims(as most of the users are ;) ) when they feel data need to be moved to archive.

Thanks and regards
Mridul

Arabic and Far East text saving as

I have a database that captuers user comments, however when comments are
posted using either Arabic or Far East characters the only thing that shows
up is a series of ? marks. These question marks also appear through SQL
Reporting Services. This haapens when the text is added via ad-hoc insert
query or though a SP.
I can open SQL Management Studio, open the table and insert the Arabic or
Chinese character ditectly into the table and the Arabic or Chinese text
will appear correctly in SQL Reporting Services.
The system configuration is:
SQL 2005 Standard
Windows Server 2003 Standard SP1
The table fields are delcared as NVARCHAR and all the parameters in the SP
are NVARCHAR as well.
Any ideas on how to resolve this?
Thank you
Marc HensleyAre the comment strings preceded by the 'N' prefix? For example, see
http://support.microsoft.com/?kbid=239530. Though targeted to earlier
versions, it still applies to SQL Server 2005.
David Barber [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Marc Hensley" <marc.hensley@.newsgroups.nospam> wrote in message
news:%23RYrtjGRGHA.2436@.TK2MSFTNGP11.phx.gbl...
> I have a database that captuers user comments, however when comments are
> posted using either Arabic or Far East characters the only thing that
shows
> up is a series of ? marks. These question marks also appear through SQL
> Reporting Services. This haapens when the text is added via ad-hoc insert
> query or though a SP.
> I can open SQL Management Studio, open the table and insert the Arabic or
> Chinese character ditectly into the table and the Arabic or Chinese text
> will appear correctly in SQL Reporting Services.
> The system configuration is:
> SQL 2005 Standard
> Windows Server 2003 Standard SP1
> The table fields are delcared as NVARCHAR and all the parameters in the SP
> are NVARCHAR as well.
> Any ideas on how to resolve this?
> Thank you
> Marc Hensley
>|||Thank You -- That is what it was. Sent the issue back to the ASP Guys to
correct.
Marc
"David Barber [MS]" <dbarber@.microsoft.com> wrote in message
news:exhfbjHRGHA.4572@.TK2MSFTNGP10.phx.gbl...
> Are the comment strings preceded by the 'N' prefix? For example, see
> http://support.microsoft.com/?kbid=239530. Though targeted to earlier
> versions, it still applies to SQL Server 2005.
> --
> David Barber [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Marc Hensley" <marc.hensley@.newsgroups.nospam> wrote in message
> news:%23RYrtjGRGHA.2436@.TK2MSFTNGP11.phx.gbl...
> shows
>sql

Apply policy to restrict records

Hello,
It is possible to restrict the records viewed by an user
using policies instead of creating a view?
If it is possible, how can i do it?
Best regardsCC
You cannot restrict 'some rows' from viewing without creation of view.
You can deny SELECT on the whole table not just on some rows.
Why do you want to that without a view? It is a classic for such kind of
operations.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
> Hello,
> It is possible to restrict the records viewed by an user
> using policies instead of creating a view?
> If it is possible, how can i do it?
> Best regards|||Hi Uri,
Someone ask me this situation and i told him that it
wasn't possible to do it but... never knows and i need to
ask it.
Thanks a lot,
Best regards
>--Original Message--
>CC
>You cannot restrict 'some rows' from viewing without
creation of view.
>You can deny SELECT on the whole table not just on some
rows.
>Why do you want to that without a view? It is a classic
for such kind of
>operations.
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
>> Hello,
>> It is possible to restrict the records viewed by an user
>> using policies instead of creating a view?
>> If it is possible, how can i do it?
>> Best regards
>
>.
>

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!

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 unable to connect Sql Server 2005

Hi,

We recently installed Sql 2005 server and it was working fine. But today one of our user reported that they could not able to connect the sql server 2005 from the application. When I checked the error log, I found the following error for every one minute. We restarted the sql service on this sql 2005 instance but still we are getting the same error.

SSPI handshake failed with error code 0x80090311 while establishing a connection with integrated security; the connection has been closed.

Could any one help us on that?

Regards,

Sankar

Hello Sankar. There appears to be several possibilities as to what is going on so i will provide the links that I think are the most relevant.

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

http://support.microsoft.com/kb/247931/en-us

OJ has suggested this as a possible means for tracing it - I am not sure of your setup so it may or may not be useful.

0x80090311 error refers to "No authority could be contacted for authentication" which means the user cannot contact AD to get a ticket. I suggest turning on Kerberos logging and using Netmon to trace the authentication routes. You can find detailed info on how to troubleshoot Kerberos here.

http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx

Let us know if you have any further questions or if this helps. If you do have further questions/problems please try specifying more details about your setup, e.g. are db and web server on same server? what os? what update revisions, etc.

|||

Hi, SanKar

Is your computer in a Domain? If so, it seems the problem that no KDC can be contacted. Suggest:

1) Disjoin the domain and rejoin your computer to the domain.

2) nslookup

type " <FQDN> of your client computer " see which DC returned.

3) ping <KDCname> if you know the hostname of your DC.

ping -a <ipaddress of your DC>, see whether FQDN returned.

4) Try use "SetSpn.exe" to manually register SPN, see whether it succeeds.

5) Or restart your client computer, try to use a domain account to log on, see what happens.

Good Luck!

Ming.

|||

Thanks all for your reply.

We rebooted the sql server. The error is not getting displayed after the server reboot.

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

Hi dear reader

I made an application that uses a Sql Server 2005 Express DataBase.

In the database I made a application role.

When the user logs into my application I run this procedure:

If Not sqlConnectionCR Is Nothing Then

If Not sqlConnectionCR.State = ConnectionState.Open Then

sqlConnectionCR.Open()

SqlConnection.ClearAllPools()

ConsultasSqlCommand = New SqlCommand

ConsultasSqlCommand.CommandType = CommandType.Text

ConsultasSqlCommand.CommandText = "sp_setapprole 'appRole', 'drowssap"

ConsultasSqlCommand.Connection = sqlConnectionCR

ConsultasSqlCommand.ExecuteNonQuery()

End If

Else....

I understand that this procedure connects to my sqlserver database as my application role

Ok, so far no problems in reading and manipulating data.

The problem comes with the reports in my application. For example: I have a reportviewer with a serverreport but when I try to show the report gives an error about permissions and grant access....

I think that is because the Server Report uses the user account (domain/user) to read the database. No user (besides admin) has access permissions in the database (only admin and application role).

So, my cuestion is: How can I tell Report Server to use the application role to display reports?

Thank you for your time and help.

Giber

Your report application should also set the application role, which should have access to the database.

Thanks
Laurentiu

|||

I develop my Server Reports in SQL Server Business Intelligence (VS2005)

I can't seem to find where or how to tell BI how to connect to the DB using the application role.

To connect im using just: "Data Source=localhost\SQLEXPRESS;Initial Catalog=ConsultasSql" in the data view seccion of the report (rdl) designer.

Greetings

|||

I am not familiar with Server Reports or SQL Server Business Intelligence, but hopefully explaining how application roles work may be useful.

Application roles or approles are database-scoped principals that require no permissions (other than being able to access the database where they are defined) to be set as the execution context at run time, but require the knowledge of a password. Approles can only be set at ad-hoc level, this means that the call to sp_setapprole cannot be part of a stored procedure body or within a user defined transaction. More detailed information is available on BOL: Sp_setapprole http://msdn2.microsoft.com/en-us/library/ms188908.aspx

If the Server Reports definition allow you to define ad-hoc statements, it may be possible to call sp_setapprole as the first statement. I would recommend asking for information on how to customize these reports in the SQL Server Integration Services forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1).

An alternative could be to use the new impersonated mechanism (EXECUTE AS) or digital signatures in a stored procedure or user defined function to retrieve the data from the database without granting permissions beyond executing the SP. You can find more information on this topic in BOL, a good starting point is Context switching: http://msdn2.microsoft.com/en-us/library/ms188268.aspx

-Raul Garcia

SDE/T

SQL Server Engine

|||

Im putting this thread under SQL Reporting Services Forum.

Also Im going to check that Execute As clause.

Tnx a lot

Giber

Application Role And SSRS

Hi dear reader

I made an application that uses a Sql Server 2005 Express DataBase.

In the database I made a application role.

When the user logs into my application I run this procedure:

If Not sqlConnectionCR Is Nothing Then

If Not sqlConnectionCR.State = ConnectionState.Open Then

sqlConnectionCR.Open()

SqlConnection.ClearAllPools()

ConsultasSqlCommand = New SqlCommand

ConsultasSqlCommand.CommandType = CommandType.Text

ConsultasSqlCommand.CommandText = "sp_setapprole 'appRole', 'drowssap"

ConsultasSqlCommand.Connection = sqlConnectionCR

ConsultasSqlCommand.ExecuteNonQuery()

End If

Else....

I understand that this procedure connects to my sqlserver database as my application role

Ok, so far no problems in reading and manipulating data.

The problem comes with the reports in my application. For example: I have a reportviewer with a serverreport but when I try to show the report gives an error about permissions and grant access....

I think that is because the Server Report uses the user computer account (domain/user) to read the database. No user (besides admin and application role) has access permissions in the database.

So, my cuestion is: How can I tell Report Server to connect to the DB using the application role in the DB?

Thank you for your time and help.

Giber

I don't see how you could do this. You could try experimenting by placing the call to sp_setapprole within the query window just before the select to see if it would execute the query in the correct context. I'm not sure whether RS reuses connections to execute multiple dataset queries so you may need to put this in every query. Like I said, never tried it, so you'll need to experiment.|||

All my reports use stored procedures to populate the DataSet.

I put Execute As in the stored procedure to impersonate the user:

ALTER PROCEDURE [dbo].[usp_Cheque]

@.CRS nvarchar(50)

EXECUTE AS 'dbo'

AS

BEGIN

DECLARE @.Temp AS nvarchar(50)

SET NOCOUNT ON;

CREATE TABLE #Temp(CRS BigInt)

WHILE LEN(@.CRS) > 0

IF CHARINDEX(',',@.CRS) = 0

BEGIN

SET @.Temp = @.CRS

INSERT INTO #Temp VALUES(@.Temp)

SET @.CRS = ''

END

ELSE

BEGIN

SET @.Temp = LEFT(@.CRS,CHARINDEX(',',@.CRS)-1)

INSERT INTO #Temp VALUES(@.Temp)

SET @.CRS = RIGHT(@.CRS,LEN(@.CRS)-LEN(@.Temp)-1)

END

END

SELECT

Nombre,

NomCheque,

NumCR,

FechaCR,

Importe

FROM

CatProveedores INNER JOIN ContraRecibos

ON CatProveedores.CveProveedor = ContraRecibos.CveProveedor

WHERE

NumCR IN (SELECT CRS FROM #Temp)

But Im still getting that EXECUTE PERMISSION denied on usp_Cheques .....dbo' Error when trying to display the report

I have to give users Domain\Administrator Windows Role so they view the reports and then change their users back to Domain\Users when reporting is done...

|||

Let me try to explain it again (my english is not so good)

When a user try to log in the database (throught my application), the application run a setapprole. So the user is not the one connecting directly to the database, if fact, no user can connect to the database (only domain\administrators). Is the application role (that as all privileges) the one that give access to tables, sp, view, etc...

How can I do so in Reporting Services? RS tries to enter the database as the computer user, but as I said before, no computer user can enter the database, I need to tell RS that when trying to connect to the database, run the set approle command.

How do I do that?

Thank's for your attention.

Giber

|||

Hi Giber,

What are the credentials you are using for your datasource for stored procedures?

If you use sa or some admin role for data source credentials, the report should be able to run the stored procedure in the database. You can try running the report(preview) in local mode.

Also, if you are trying to control access for your reports, you can create different roles and groups and assign groups to these roles in SQL Server 2005 Reporting services. Or you can control the access for each report by giving appropriate permissions directly to that particular user or group. You will have to manuaaly ensure that the groups you make for Reporting Services match with the ones in SQl Server database.

Hope this will solve your problem.

Regards

Application not connecting to DB on restart of PC?

Hi,

i am stuck in a strange situation.
I

have successfully built my application setup through InstallSheild 12.

Which first installs SQL Express user define instance as a

pre-requisite and then install my application files. First time when i

run my application (without restarting the pc), it connects

successfully with user define instance.
But as i restart my PC and try to open my application, it is not connecting with database.
i am using the following command line to install SQL Express 2005 user define instance:

"/qn ADDLOCAL=SQL_Engine INSTANCENAME=MyInstance SECURITYMODE=SQL SAPWD="test" AUTOSTART=1"

if

i change Remote connection to "using both TCP/IP and named pipes"

through "SQL Server Surface Area Configuration" and then restart my pc

again. My application connects successfully with Database.

Please

guide me where i am getting wrong in building setup. do i have to add

something in my command line to not get this error

Please provide more information...

What error are you getting?

How are you connecting to SQL Express? (i.e. connection string)

What is different from the first time you connect at installation and the second time you connect after re-boot?

Are you using ODBC, OLEDB or Shared Memory to connect?

Mike

|||Hi,
thanks for the reply.
i am installing my installer on WinXP machine.
i am using the following connection string to connect with database.

Connection String:
--
Connection.ConnectionString = "packet size=4096;user id=sa;pwd=test; data source = (local)\\MyInstance;initial catalog = MyDB";

Error getting when connecting to SQL Mangmnt Studio:
-
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote connections. (provider: Shared Memory Provider, error: 40 -
Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

when first time i install , In "SQL Server Surface Area Configuration" it is checked on "Local Connection Only" and connects successfully.
When i restart my pc and open application it doesnt connect .I open "SQL Server Surface Area Configuration", still "Local Connection Only" is checked but when i check on "using both TCP/IP and named pipes".
and then start my applicaion it connects successfully.|||Hi,
i am using following namespace for connection object.
using System.Data.SqlClient;

application login , but user security

Hi everybody...please don't flame me for my ignorance.
We are in the process of migrating from mainframe application using
vsam files to MICROFOCUS and SQL server
Here is the senario we are at now :
In mainframe, the application might be able to read and write to the
file, but the write access is controled by the RACF (security software
like active directory). In other words, even though USER A can execute
the application, he cannot write to it, because the security software
doesn't allow USER A to write to the file.
In the new environment, our online application are replaced by a
product called MICROFOCUS -ES_MTO. ES-MTO connects to sqlserver via an
application userid (lets say APPL1). USERA logins in to the ES-MTO
using login id USERA, but then ES-MTO connects to sqlserver using
APPL1. APPL1 has read/write authority on the tables. USERA should be
able to execute the application, so he can read the table, but
shouldn't be able to write to it.
The application is however a read/write application.
I hope I was clear enough on my sceneraio.
What I am hoping to find out is, how can I still use sqlserver to check
permission using the real user login id , when the application uses the
application userid to connect ?
Am I making sense ?
Any help or input is greatly appreciated .
ThanksSQL Server security context is determined by the login used to connect to
SQL Server or an application role enabled after the connection is made. If
ES_MTO uses a single login, you won't be able to implement a SQL Server
security model that allows you to control access based on an individual
user's identity unless your application code can conditionally enable an
application role. I know nothing about ES_MTO so I can't comment on whether
or not that approach is feasible. You can read about application roles in
the SQL Server Books Online
Hope this helps.
Dan Guzman
SQL Server MVP
"sql rookie" <anytasks@.gmail.com> wrote in message
news:1114703232.008696.232480@.g14g2000cwa.googlegroups.com...
> Hi everybody...please don't flame me for my ignorance.
> We are in the process of migrating from mainframe application using
> vsam files to MICROFOCUS and SQL server
> Here is the senario we are at now :
> In mainframe, the application might be able to read and write to the
> file, but the write access is controled by the RACF (security software
> like active directory). In other words, even though USER A can execute
> the application, he cannot write to it, because the security software
> doesn't allow USER A to write to the file.
> In the new environment, our online application are replaced by a
> product called MICROFOCUS -ES_MTO. ES-MTO connects to sqlserver via an
> application userid (lets say APPL1). USERA logins in to the ES-MTO
> using login id USERA, but then ES-MTO connects to sqlserver using
> APPL1. APPL1 has read/write authority on the tables. USERA should be
> able to execute the application, so he can read the table, but
> shouldn't be able to write to it.
> The application is however a read/write application.
> I hope I was clear enough on my sceneraio.
> What I am hoping to find out is, how can I still use sqlserver to check
> permission using the real user login id , when the application uses the
> application userid to connect ?
> Am I making sense ?
> Any help or input is greatly appreciated .
> Thanks
>|||sql rookie wrote:
What I am hoping to find out is, how can I still use sqlserver to check
permission using the real user login id , when the application uses the
application userid to connect ?
Am I making sense ?
My response:
I am unsure what you mean by using 'sqlserver to check permission using the
real user login id'?
If you setup the security on the MicroFocus environment using SQLServer
security (uid and password). This userid can be granted R/W access to the
tables while the real user id will not be granted them. You can then contro
l
access to writing/updating the tables via the application. If you want/need
to have the user be able to read the information outside of the application
(Access linked tables, Excel Queries, etc) you can grant the 'real user id'
read access to the tables/views.
Does this cover your question'
Mike
--
Mike Mattix
CP Kelco, Inc
Okmulgee, OK
"sql rookie" wrote:

> Hi everybody...please don't flame me for my ignorance.
> We are in the process of migrating from mainframe application using
> vsam files to MICROFOCUS and SQL server
> Here is the senario we are at now :
> In mainframe, the application might be able to read and write to the
> file, but the write access is controled by the RACF (security software
> like active directory). In other words, even though USER A can execute
> the application, he cannot write to it, because the security software
> doesn't allow USER A to write to the file.
> In the new environment, our online application are replaced by a
> product called MICROFOCUS -ES_MTO. ES-MTO connects to sqlserver via an
> application userid (lets say APPL1). USERA logins in to the ES-MTO
> using login id USERA, but then ES-MTO connects to sqlserver using
> APPL1. APPL1 has read/write authority on the tables. USERA should be
> able to execute the application, so he can read the table, but
> shouldn't be able to write to it.
> The application is however a read/write application.
> I hope I was clear enough on my sceneraio.
> What I am hoping to find out is, how can I still use sqlserver to check
> permission using the real user login id , when the application uses the
> application userid to connect ?
> Am I making sense ?
> Any help or input is greatly appreciated .
> Thanks
>

Application is hung

Hello Friends,
A user is calling stating that their application is hung - sitting with an hour glass or with the browser not responding. Assume you know the server and the instance and that the server supports multiple instances.

You need to determine if the database could be contributing to the problem or could be the cause of the problem. Describe at a high level what process you would follow to rule the database out or in as the problem.

Any idea!

Thanks,
FrankHello Friends,
A user is calling stating that their application is hung

I'll leave that alone....|||Hello Friends,
A user is calling stating that their application is hung - sitting with an hour glass or with the browser not responding. Assume you know the server and the instance and that the server supports multiple instances.

You need to determine if the database could be contributing to the problem or could be the cause of the problem. Describe at a high level what process you would follow to rule the database out or in as the problem.

Any idea!

Thanks,
FrankAsk your instructor or TA which help desk you should call for that kind of problem on these servers?

-PatP|||I'll leave that alone....Now pay attention! They are claiming that their application is hung. That is a different kind of issue, not bragging!

-PatP|||Didn't your sweetie in high school used to call you her applicator?

..damn this is more corral stuff...

Any good question lately?

Oh, and btw, it's never the database...|||High level ... hmmm ... do a hard reset on the application server! If the problem goes away it's the application. If that fails, do a hard reset on the database server. If the problem goes away, the application caused the problem to appear on the database server. If the problem still exists, press the red button in the server room and switch over to the generators.

;)|||Now pay attention! They are claiming that their application is hung. That is a different kind of issue, not bragging!

-PatP
No brag ... just fact!

application development and permissions

Using SS2000 SP4. We have an application that will use a single user to logi
n
from the application after development. We will use only stored procedures
and views to access the data.
I was thinking of creating a "developer" login for the developer and
assigning it to the db_owner role. As I understand it, this would make the
owner of the tables, stored procedures and views "dbo". After development,
assuming there is no dynamic sql used, would I then be able to give execute
permission on the stored procedures and select permissions on the views to
the application user and have everything work?
Thanks,
Dan D.Dan
Sure
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A56A2358-08D0-4C65-9D2F-564BF9873B6D@.microsoft.com...
> Using SS2000 SP4. We have an application that will use a single user to
> login
> from the application after development. We will use only stored procedures
> and views to access the data.
> I was thinking of creating a "developer" login for the developer and
> assigning it to the db_owner role. As I understand it, this would make the
> owner of the tables, stored procedures and views "dbo". After development,
> assuming there is no dynamic sql used, would I then be able to give
> execute
> permission on the stored procedures and select permissions on the views to
> the application user and have everything work?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Uri Dimant" wrote:

> Dan
> Sure
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A56A2358-08D0-4C65-9D2F-564BF9873B6D@.microsoft.com...
>
>