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
No Comments so far
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>
Apologies for the CAPTCHA but the SPAM was killing the site. I was getting hundreds each day.
This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.