[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Help on assigning access to ms sql databases

Posted on 2012-03-10
18
Medium Priority
?
214 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 668 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 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 1332 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 25

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 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 1332 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 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 1332 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 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 1332 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 668 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

649 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