sp_setapprole was not invoked correctly

[Jørgen Nilsen [PRIDE ASA]]

I get the error "sp_setapprole was not invoked correctly" when I try to run
sp_setapprole second time in a connection.

I have one role with read access and one role with write access(without read
access).
How can I give access to both roles in one connection ?

Jørgen

[Gert E.R. Drapers]

You can not, Books Online states for sp_setapprole
After an application role is activated with sp_setapprole, the role cannot
be deactivated in the current database until the user disconnects from SQL
Server.

-GertD

"Jørgen Nilsen [PRIDE ASA]" <j...@pride.no> wrote in message
news:i7NH6.200$fB4.17...@news.telia.no...

[Jørgen Nilsen [PRIDE ASA]]

Thanks for the answer.
I have a Admin-user that have access to server roles System-, Security
administrators, and db_owner++.
When I run sp_setapprole the access to this roles is lost.
Is it really true ?
What can approles be used to if we only can have one active role ?

Any ide on how I can use sp_setapprole in connection with other roles ?

Jørgen

"Gert E.R. Drapers" <ge...@sqldev.net> wrote in message
news:#2iVKSt0AHA.1828@tkmsftngp03...

[Tibor Karaszi]

Jörgen,

> What can approles be used to if we only can have one active role ?

To change user context so you get a predictable permission context.

If you would keep any user or role context then that could have a deny and
the app role might because of that no be able to perform whatever the
designers might have in mind. At least this is what I think that the
designers were thinking...

You'd need to open another connection to get to another role/user.
--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.

"Jørgen Nilsen [PRIDE ASA]" <j...@pride.no> wrote in message

news:hVSH6.224$fB4.17...@news.telia.no...