using sa with SQL 2005 problem??

[Les Caudle]

I un-installed SQL 2000 developer's edition and installed SQL 2005 developer's
edition in mixed mode security with the exact same sa password. I then
attached my old databases.

Even thought the web server is located on the same machine, I can no longer us
the sa user/pass in my connection strings.

The Open fails with what seems like a lack of permssion.

Oddly enough, I can connect to SQL Server using the SQL Server Management Studio
using the sa user/pass (so sa certainly does have enough permissions)

1. How can I get the sa to work? I'll probably move everything to integrated
security logins, but I've got a lot of code that would need to be changed.

2. When using an integrated connection string:

server=localhost;Integrated Security=SSPI;Initial Catalog=testDb

I had to give ASP.NET owner privileges for the database or I'd get failures on
EXECUTE. This seems a step in the wrong direction. None of my 'Pro' level
books on ASP.NET seem to mention the proper setup of security settings for the
databases. What is the correct way to give the ASP.NET account enough
permissions to perform most database tasks? Or, should I setup a separate
user/pass just for SQL Security.

 

[sirfunusa]

Show your connection string when you use a login.

http://www.connectionstrings.com/

 

[Garth Wells]

I strongly encourage you to take the oppurtunity to fix your security hole.
Using sa, even on dev boxes, is really bad practice. I usually create a
login per application and give it dbo access to the target database.Of
course, the best approach is to use stored procedures and only give
the login/user permissions to those, but I'm just not there yet. Baby
steps...