login failed for 'user' (Application Role)

I've created a database in SQL Express and I have a Windows form attempting to connect to it through SQL Authentication. Connection string:

private string connString = @."Data Source=.\sqlexpress;Initial Catalog=SQLTestDatabase;User ID=SearchAppRole; Password=password;";

The role I have added to the database is an Application Role. It has been added to the Database permissions with Grant checked for "Select" and "Authenticate".

If I test this with query analyzer, it returns expected results (if I remove Grant from 'Select', it fails)

sp_setapprole 'SearchAppRole', 'password'

select * from recipe

If I edit my connection string (for testing purposes) to use the sa account, the application can connect and run the Select statement:

private string connString = @."Data Source=.\sqlexpress;Initial Catalog=SQLTestDatabase;User ID=sa; Password=sa_password;";

However, I cannot get the application to successfully logon and run the select statement when using the user id and password of the Application Role. I get error:

System.Data.SqlClient.SqlException: Login failed for user 'SearchAppRole'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj

I can't find much information on Application Role...I just want one basic permission for the application as a whole. Any help is appreciated. Thanks.

You will first have to connect with a valid user on the database to set the application role. The user can be a dummy user having no access to the actual stored objects. But for using the command you will have to first open a connection to the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Ok. I'm sure this works, and I think I found an example of it here on these forums. However, I decided to just add a User to the database and work with that. I'm not sure that I really need to use Application Role and I don't know why it isn't more straightforward. Thanks.sql

Login & owner of object

Hi Guys,
When I'm creating login and assinging to DBO role it is still creating
object name with login.objectname instead of dbo.object name..
Am I doing anything wrong or I have to do anything else to do this.
Thanks
- NT
Hi NT
There is no DBO role.
There is a user DBO and role called db_owner. Although the DBO user is in
the db_owner, other users can be in the db_owner role as well.
Every login has a user name in every database. User names can be members of
database roles.
When using Query Analyzer you can see the current user's name by running
this command:
SELECT user_name()
Only the user DBO will have all the objects she creates owned by DBO, by
default. However, a user in the db_owner role may create objects owned by
the DBO user, if the owner is stated explicitly:
CREATE TABLE dbo.new_table
(col1 ...
col2...
)
etc.
Please read about users and database roles in the Books Online for more
information.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"NT" <NT@.discussions.microsoft.com> wrote in message
news:205A1292-BCEE-467F-99BA-D1856C81737F@.microsoft.com...
> Hi Guys,
> When I'm creating login and assinging to DBO role it is still creating
> object name with login.objectname instead of dbo.object name..
> Am I doing anything wrong or I have to do anything else to do this.
> Thanks
>
> - NT

Login & owner of object

Hi Guys,
When I'm creating login and assinging to DBO role it is still creating
object name with login.objectname instead of dbo.object name..
Am I doing anything wrong or I have to do anything else to do this.
Thanks
- NTHi NT
There is no DBO role.
There is a user DBO and role called db_owner. Although the DBO user is in
the db_owner, other users can be in the db_owner role as well.
Every login has a user name in every database. User names can be members of
database roles.
When using Query Analyzer you can see the current user's name by running
this command:
SELECT user_name()
Only the user DBO will have all the objects she creates owned by DBO, by
default. However, a user in the db_owner role may create objects owned by
the DBO user, if the owner is stated explicitly:
CREATE TABLE dbo.new_table
(col1 ...
col2...
)
etc.
Please read about users and database roles in the Books Online for more
information.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"NT" <NT@.discussions.microsoft.com> wrote in message
news:205A1292-BCEE-467F-99BA-D1856C81737F@.microsoft.com...
> Hi Guys,
> When I'm creating login and assinging to DBO role it is still creating
> object name with login.objectname instead of dbo.object name..
> Am I doing anything wrong or I have to do anything else to do this.
> Thanks
>
> - NT

Login & owner of object

Hi Guys,
When I'm creating login and assinging to DBO role it is still creating
object name with login.objectname instead of dbo.object name..
Am I doing anything wrong or I have to do anything else to do this.
Thanks
- NTHi NT
There is no DBO role.
There is a user DBO and role called db_owner. Although the DBO user is in
the db_owner, other users can be in the db_owner role as well.
Every login has a user name in every database. User names can be members of
database roles.
When using Query Analyzer you can see the current user's name by running
this command:
SELECT user_name()
Only the user DBO will have all the objects she creates owned by DBO, by
default. However, a user in the db_owner role may create objects owned by
the DBO user, if the owner is stated explicitly:
CREATE TABLE dbo.new_table
(col1 ...
col2...
)
etc.
Please read about users and database roles in the Books Online for more
information.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"NT" <NT@.discussions.microsoft.com> wrote in message
news:205A1292-BCEE-467F-99BA-D1856C81737F@.microsoft.com...
> Hi Guys,
> When I'm creating login and assinging to DBO role it is still creating
> object name with login.objectname instead of dbo.object name..
> Am I doing anything wrong or I have to do anything else to do this.
> Thanks
>
> - NT