Solved

Help on assigning access to ms sql databases

Posted on 2012-03-10
18
192 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:rayluvs
  • 10
  • 5
  • 3
18 Comments
 

Author Comment

by:rayluvs
ID: 37705202
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
 
LVL 20

Accepted Solution

by:
ElrondCT earned 167 total points
ID: 37706185
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
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 333 total points
ID: 37706211
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:rayluvs
ID: 37706375
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
 

Author Comment

by:rayluvs
ID: 37706379
oops!  by  out code, we meant Its Our Code
0
 
LVL 20

Expert Comment

by:ElrondCT
ID: 37706703
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
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 37706760
If any objects are created then you can assign the role of ddladmin.
0
 

Author Comment

by:rayluvs
ID: 37707071
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
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 333 total points
ID: 37707375
For question 1 you do not need dbowner just ddladmin.

For question 2 just datareader and datawriter.
0
 

Author Comment

by:rayluvs
ID: 37708176
Thank you very much.

So theoretically we are doing the right thing.

Next step is the recommendation of step-through to identify the
0
 

Author Comment

by:rayluvs
ID: 37708183
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
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 333 total points
ID: 37708216
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
 

Author Comment

by:rayluvs
ID: 37708237
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
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 333 total points
ID: 37708369
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
 
LVL 20

Assisted Solution

by:ElrondCT
ElrondCT earned 167 total points
ID: 37709836
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
 

Author Comment

by:rayluvs
ID: 37709955
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
 

Author Comment

by:rayluvs
ID: 37720775
We'll be doing this task this week.... Thanx!
0
 

Author Comment

by:rayluvs
ID: 37758240
Couldn't check the problem but have coordinate to do debuggung next week
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question