Solved

Help on assigning access to ms sql databases

Posted on 2012-03-10
18
186 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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now