Help on assigning access to ms sql databases

We have a problem that the tech assign full access to database and we think shouldn't be.  We were told because the apps needs it.
rayluvsAsked:
Who is Participating?
 
ElrondCTCommented:
If the application is not changing the structure of the database, there is no reason that dbowner should be required. If the application won't work correctly, either it needs to do more than you understand, or it's written badly. Do you have control of the source code, or is this coming from an outside party? Is the "tech" someone who works for you, for the app's author, or independent? If it's commercial software (i.e., sold generally), can you tell us its name? Someone here may have experience with it.

In the VB app I have that uses SQL Server, dbowner is only needed if someone wants to grant another user permission to connect to the database (I have a function in the app that allows adding users). I simply check for that permission when someone wants to do the action.
0
 
rayluvsAuthor Commented:
To be more specific, we understand that when a DB is set in production, the MS Sql access assign with datareader and datawrite should be sufficient.  But we have seen that the apps will not work as design if we don’t assign dbowner.  

The problem we think with dbowner is that the user can even “drop” table and very configurable access to the database.

- The MS Sql is 2000 and 2005
- The apps are in VB2005
- the users are in domain windows server

The way the apps access the databases are by windows authentication, not sql authentication.

Please advice on the proper access we should give user, EE speculation of why the tech would says that is necessary to assign dbowner and finally, any observation when we create a new domain user, should additional task should be done.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Typically the requirement to use dbowner to access the database from an application is due to permission requirements like:

TRUNCATE TABLE
CREATE OR DROP anything
Even ALTER

If the application does these things, then it will be more difficult to isolate permissions.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rayluvsAuthor Commented:
Answers:

   Do you have control of the source code, or is this coming from an outside party?
   Answ: We have developed it; out code

   Is the "tech" someone who works for you, for the app's author, or independent?
   Answ: work for us

   If it's commercial software (i.e., sold generally), can you tell us its name? Someone
   here may have experience with it.
   Answ: not commercial

As for the typical access, non of the apps truncate tables, creates, drops or alter

Now maybe, just maybe it does create views, but not sure; we'll check on Monday.

Two questions:

   1.  If any code has to create VIEWS, do we need to give access as DBOWNER?

   2. The apps only reads and writes, what should the be recommended access?
       only dataread and datawrite, or is there is additional?
0
 
rayluvsAuthor Commented:
oops!  by  out code, we meant Its Our Code
0
 
ElrondCTCommented:
OK, your tech should be able to tell you exactly what the application is doing that requires dbowner, either because he knows already, or because he can run the app (perhaps in debug mode) in an account without dbowner permission and see where the problem occurs. If you can tell us what command(s) are causing the error, we can try to help you figure out alternative ways of doing what you need to do.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
If any objects are created then you can assign the role of ddladmin.
0
 
rayluvsAuthor Commented:
Understood, but what about  our questions::

   1.  If any code has to create VIEWS, do we need to give access as DBOWNER?

   2. The apps only reads and writes, what should the be recommended access?
       Is dataread and datawrite sufficient, or is there is additionals we should take
        in consideration?
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
For question 1 you do not need dbowner just ddladmin.

For question 2 just datareader and datawriter.
0
 
rayluvsAuthor Commented:
Thank you very much.

So theoretically we are doing the right thing.

Next step is the recommendation of step-through to identify the
0
 
rayluvsAuthor Commented:
Wait, didn't see "ddladmin".  If we give this type of access, isn't like an Admin typr of access? That is, wouldn't be similar as to dbowner where it access to configuration, drop, etc.

Please advice.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
ddl is Data definition language admin. So the access is allowing them to create objects. It is in the database roles. It is not like dbowner.
0
 
rayluvsAuthor Commented:
understood.

We are going to check the code tomorrow, but can it be possible the the actual SQL instances be corrupted in some way? (I mean, if we check the code and there is no lines of code that Truncates Create, Alter or Drop Tables, is it possible that the instance is corrupted?)
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
In SQL 2000 there is not a ddladmin. So if those permissions are indeed needed then dbowner for 2000 may have to be used.

I am not sure why the instance would be corrupted if things are working well.
0
 
ElrondCTCommented:
If you run through the code in debug mode in an account without dbowner, you'll be able to see at exactly what point the code is failing. If the reason isn't obvious, or the resolution isn't obvious, post back with the code that's causing the problem. I don't know why an instance corruption would cause this problem, but if that's it, we should be able to discern that from the code (because, for instance, running that command doesn't cause a problem for us).

However, you could also test for instance corruption by installing a new instance of SQL Server (download the Express version for free, if needed, assuming the database isn't more than 4 GB in size) and using that.
0
 
rayluvsAuthor Commented:
Thanks both.

Reading the last entry, I think is not related to instance problem because we 2 SQL instance one in 2000 and the other in 2005; and both has the same issue.

We'll proceed step thru the apps to identify where is the call is being made from.

Thanx
0
 
rayluvsAuthor Commented:
We'll be doing this task this week.... Thanx!
0
 
rayluvsAuthor Commented:
Couldn't check the problem but have coordinate to do debuggung next week
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.