Application role security

[redlam]

Hi there.
My question is:
When using application role security to access a SQL2000 database from (lets say) a VB6 application, is it necessary to connect to the database first using a valid SQL or NT user account? In order words, does the application first connect by 'sa' or another user and THEN execute the sp_setapprole stored procedure? I was hoping to restrict access to the database to a single application role but it seems that's not the way it works...
Anyone know more details?

Much appreciated.

 

[tlbroadbent]

The connection must be made using a valid SQL server or NT login before running the stored procedure. This login doesn't need permissions on the database to be accessed. You can restrict database access to a single application role though it is likely that some users may be granted access through other means such as database roles.

From SQL BOL:[ul]"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.

"If ad hoc access to a database is not required, users and Windows NT groups do not need to be granted any permissions because all permissions can be assigned by the applications they use to access the database."[/ul] Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[redlam]

Thanks Terry. It didn't occur to me that the "connecting" user didn't need permissions to the database - which, of course, now that you brought it up, makes a lot of sense.