Results 1 to 10 of 10

Thread: SP execution

  1. #1
    Join Date
    Feb 2005
    Location
    India
    Posts
    217

    Unanswered: SP execution

    I have two users, a normal user and a dbo.

    The normal user has created a SP.
    My dbo user is NOT able to execute it.

    I feel that dbo should be able to execute the SP, eventhough he has NOT created it because he is DBO!

    Can anyone put some light on the issue?

    Thank you!

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    This can't be ASE.

    In ASA and Sybase IQ, there is no concept of ASE dbo. So the dbo is just another login.

  3. #3
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    I am using ASE,

    select @@version
    gives the folloiwng

    Adaptive Server Enterprise/12.5.1/EBF 11420/P/Sun_svr4/OS 5.8/ase1251/1823/64-bit/FBO/Wed Sep 17 09:05:24 2003

  4. #4
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Your views are very interesting. Unfortunately, SQL (American National Standards Instutue) was created by minds greater than ours, without taking your ideas into account, a very long time ago, and it works the way it works. That method is described in the manual. The permissions systems is very thorough and robust, and the rest of us who have been using it for yonks, do not want to it changed. There is no light to be shed. Rather than seeing it as the (well-established and standards-compliant) software doesn't do what you think it should, RTFM and find out how the said software actually does work - the learning process will be less frustrating.

    The established concept is: every user can create objects, and must explicitly grant usage permissions to the users|groups|roles that they wish to allow. <dbo> is just another user, who happens to have created and owned the db. I think you might be looking for:

    Code:
    n> grant execute on <procname> to <dbo>
    n> go
    In normal practice, the <dbo> objects are granted so that they are common to all users, and the user objects are usually private to the user. Once an object becomes stable, it is re-created under <dbo> and granted to user|group|role. This allows (eg) a single copy of the stable objects; private copies of the user objects (eg. developer, latest development); all within the same db, using the same tables.

    (Greetings, Willy. Good to see you're around.)
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  5. #5
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    DerekA,

    Thank you for clearing the concept. I am using SQL server, it works differently in sql server.

    DBO user has all permissions to objects created by normal users. Things are different in Sybase.

  6. #6
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Actually that depends on who the DBO really is.

    If DBO == 'sa', then the DBO will have all the rights (because of the inherent 'sa' rights). COnversely, if the DBO is a normal user, then the rights will be more limited.

    So check your SQL Server installs and see if the DBO is 'sa' or some other user...

    Michael

  7. #7
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Things are never what they seem

    I beg your pardon, I was sure you said:
    Quote Originally Posted by ravilobo
    I am using ASE,

    select @@version
    gives the folloiwng

    Adaptive Server Enterprise/12.5.1/EBF 11420/P/Sun_svr4/OS 5.8/ase1251/1823/64-bit/FBO/Wed Sep 17 09:05:24 2003
    But now you're using MicroShaft. Oh well.

    And do check your aliases as well. Things are never the way they look with all the aliased users lying around.

    Re DBO, I would put it another way:
    - if dbo is a normal (not 'sa') user, then ANSI protections apply (if MS supports that in your release, service pack, etc) which means the user has to grant exec explicitly
    - 'sa' operates outside the protection system, so it can inspect a normal user's (or dbo's) objects. Can't say for sure what MS is doing with this one today, but this the traditional method microshafters use to get around the protection system and make things work.

    Cheers
    Derek
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  8. #8
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    Quote Originally Posted by DerekA
    I beg your pardon, I was sure you said:
    ....But now you're using MicroShaft. Oh well.
    I am regular SQL Server DBA, but recently have been put into a project which uses Sybase.



    Quote Originally Posted by mpeppler
    If DBO == 'sa', then the DBO will have all the rights (because of the inherent 'sa' rights). Conversely, if the DBO is a normal user, then the rights will be more limited.
    mpeppler,
    This is TRUE for Sybase.
    In SQL Server dbo has access to all the objects in the DB, even though dbo is NOT sa.

    Hope this is clear!

  9. #9
    Join Date
    Jul 2006
    Posts
    7
    hi
    i guess, it couldnot find the procedure. so u try to run like this
    exec <normal_user_Name>.<stored_procedure>

    rx
    Guru...

  10. #10
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    So, I am dying to know, does your question relate to Sybase or NecroSoft ?

    I do not answer SQL Server questions (except V4.9.2 of course), as the operation and codeline changes every year. If you have truble finding things on MS, you could always port it to Sybase: at least the operation is predictable.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •