Post reply

SQL SERVER 2005 Users cloning

  • June 24, 2010 at 11:42 am

    #140881

    Hi GUYS,

    I have a issue with App Team regarding some users.

    Here is my detail issue description:

    I have a clustered environment(active/passive, 2 node) with SQL SERVER 2005 SP3 64 Bit.This server has a database which is using 24/7 by different applications. All applications are making connection using a single sql login 'user1'. My question is: Is there any difference for using a single user by different applications vs individual users for each applications to access the database ?

    currently,

    user1 - app1,app2,app3,app4

    VS

    user1 - app1

    user2 - app2

    user3 - app3

    user4 - app4

    Is there any difference in performance or any other matter by using this two scenarios ?

    I appreciate your kind help.

    Thanks

    AKP

  • June 25, 2010 at 7:48 am

    #1185000

    Performance will definitively not be an issue.

    Having an user per application allows you to be more granular on permissions for each one, so that is actually not a bad practice.

    If you need to turn of access to one application you would not affect the other ones for example.

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • June 25, 2010 at 10:59 am

    #1185148

    Thanks for your response. Currently there are some blocking issues on DB server when they use single account. App team is saying if there are multiple account this will not happen, Is this true ? Is there any difference in database object/data accessing with multiple account vs single account?

    ----

    AKP

  • June 25, 2010 at 11:04 am

    #1185152

    blocking is not affected by users...it's affected by two connections selecting and updating the same row of at nearly the same time.

    ...whether it is two connections with the same user, or two connections with different users, the user is not what is affecting the blocking/contention, it's the accessing/updating of the data that is the issue.

    I'd be running something like this to find out more about any blicking issues:

    SELECT *

    FROM sys.dm_exec_requests

    WHERE wait_time > 0

    AND blocking_session_id != 0

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 25, 2010 at 12:41 pm

    #1185207

    As Lowell mentioned, it makes no difference if you use different users when they touch the same tables.

    I would actually look at the underlying code that causes the blocking. In most cases by rewriting it or using locking hints (if possible) it can be easily solved.

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply