SQL Owned

Permissions in M$ SQL Server can be a pain in the ass.

Your application needs access to the database but handing out the ‘sa’ user password is generally a bad idea. For most people this doesn’t seem to be a problem. Most example code I’ve seen uses the ‘sa’ user account. Ouch!

Besides, it’s a moot point when you’ve got the SS installed using only Windows authentication. Mixed mode can be fun but it’s really not preferable.

So, you ask, “What’s the answer?” Here’s a quick’n’dirtyâ„¢ solution, change the database owner to the account that needs access to the database. Mind you, this really only applies in DEV environments. Usually this user is either the MACHINENAME\IUSR_MACHINENAME account or MACHINENAME\ASPNET account. Depends on your favoured flavour of M$ server side goodness.

Here’s the query:

USE databaseName
GO
EXEC sp_changedbowner ‘MACHINENAME\ASPNET’
GO

(Beware: WordPress in it’s XHTML1.0 Strict goodness changes the apostrophes in the above query into politically correct versions that will break when used in a SQL query.)

You’ll then need a matching database connection string:

“Server=SERVERNAME;_
Database=databaseName;_
Trusted_Connection=True;”

Sure this method places control of that database in the hands of an anonymous user but it considerably lessens the chances of the account that has access to all your databases going astray.

To counter this, once the database and apps are in a PROD environment, depending on the required functionality, the anonymous user can be given restricted access to the database.