Just another bunch of stuff pouring out of some guy's head

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.

Comments

Leave a comment

Line and paragraph breaks automatic, HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required, never displayed)


Before you please complete the CAPTCHA below.

Time limit is exhausted. Please reload the CAPTCHA.

Sorry about the CAPTCHA but the SPAM was killing the site. I get thousands a day.