Validating User Account and Privledges 3

[colttaylor]

I don't want my application users to have any powerful sql id/passwords (sa/superuser/etc) but I want my application to be able to test that the database, tables, indeces, sp's and fields are present and create them if needed. I've decided to keep a powerful id/password pair encrypted in the application's ini file, but that leaves my application with another dependency. That id/password pair has to be tested on startup to make sure it works.

So how do I test a id/password pair without a known-to-function id/password pair? If I attempt to connect with the id/password pair which I want to test and it fails, that doesn't mean that the pair is invalid, it might just be a faulty connection string. I guess I need to create and story two id/password pairs. If either works, then I know that the connection string is ok. If either fail, then I can use the other one to recreate the damaged one.

I cannot be the only person trying to do this? What is the established best practice for startup validation?

Thanks in advance for the assistance!
Peace,
Colt.

If it's stupid but it works, it isn't stupid

 

[nigelrivett]

Depends on how the application connects but you should get a different error back for a password failure from an invalid connect string.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[dmcmunn]

This is a crude suggestion, but why not have a scheduled SQL Server job (hosted in the "master" db) that periodically verifies schema integrity for the application-specific database and makes any appropriate adjustments ?
Your app userid/password could insert a semaphore row in a table, and an insert trigger could launch a DDL integrity check on the app database.

 

[SQLSister]

My view is that is it actively harmful for the user interface to create missing items. I would not allow my user interface to do that. And once logged into SQL server with that kindo f access the user would have to log off and relogin with out it or would continue to have that access.

The dbas responsibility is to see to it that all applicable objects are inthe the database. If they aren't an error should be generated and the dba should have to fix it.

Let me give you an exapmle why this is a bad idea. Suppose a field was dropped by mistake. A field tha had critical data in it. If the application says "hey this field is missing" and creates it, now it is there but the data is missing. If the application fails and the dba has to fix it, he would restore from a backup.

Questions about posting. See faq183-874

 

[colttaylor]

Thanks for your views.
I understand the dangers you've illuminated, but I deal with a less than brilliant user and installer base. Forgiving code is job one and the (re)creation of missing tables and fields are expected features despite the potential problems.
I've decided to demand the sa password during installation and to store it in encrypted form within the ini file. I will then delete and recreate an unused table on each program startup to test that the super id/password work and refuse to run if they dont. It is not as good as I had hoped, but it will do.
Thanks again for the help!

If it's stupid but it works, it isn't stupid

 

[ESquared]

if it isn't stupid but it doesn't work, it's stupid