Re: db_owner issue. user Rol assign.

From: Ricardo Madariaga (rmadariaga@omegacl.cl)
Date: 10/05/02 

Date: Fri, 04 Oct 2002 18:07:27 -0400
From: Ricardo Madariaga <rmadariaga@omegacl.cl>

Ken
I have solved the problem by doing a workaround.
devuser and appuser will be the same. At least, this way, there will be
no table structure modifications by the user, which was the most
important issue.

Thank you very much.

Ricardo

Ken Schaefer wrote:

>I would suggest that you need a proper development methodology, you develop
>the code then you have a build routine which can "rebuild" the database into
>a production database, perhaps with the owner as dbo
>
>Cheers
>Ken
>
>--
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>"Ricardo Madariaga" <rmadariaga@omegacl.cl> wrote in message
>news:3D9C5158.2020700@omegacl.cl...
>
>>Yes. I understand.
>>You have confirmed to me that the user must be assigned the db_owner rol
>>to skip the fully qualified name use for calling SPs. Thank you
>>But if I have to use the appropriately qualified name, there will be
>>much code change and other problems when the Data Base moves somewhere
>>else where the usernames change.
>>¿how can I give the user db_owner but not let him change/create tables.?
>>
>>Ricardo
>>
>>Ken Schaefer wrote:
>>
>>>What is the problem?
>>>
>>>All I can see is that you don't want to put devuser.spname in your
>>>code...but unless the sproc is created by dbo, or the sproc is being run
>>>
>by
>
>>>the creating user you have to have an appropriately qualified name.
>>>
>>>Cheers
>>>Ken
>>>
>>>--
>>>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>>"Ricardo Madariaga" <rmadariaga@omegacl.cl> wrote in message
>>>news:2d2001c26995$dacce5a0$35ef2ecf@TKMSFTNGXA11...
>>>
>>>>Hello.
>>>>My system is NT4 sp6, MSSQL7 sp4, MDAC 2.7, IIS4, ASP
>>>>application that uses a custome DLL to manage connections
>>>>to ODBC.
>>>>
>>>>I need 2 MSSQL users:
>>>> 1.- an SP developer user
>>>> 2.- an functional application purpose user
>>>>
>>>>I need to configure a development user that must only:
>>>>- insert/edit data
>>>>- create and execute Stored Procedures (SPs).
>>>>This user must not modify table structures or create
>>>>anything but SPs.
>>>>Unfortunatly, asigning db_owner rol gives these
>>>>permissions.
>>>>
>>>>So for this, I assigned the db_datawrite/db_datareader rol
>>>>to the user. For the SP creation permission, I manualy did
>>>>a 'grant create procedure to <user>' command. I also
>>>>created a special rol which I gave execute permissions on
>>>>all SPs of the database and asigned this rol to that user.
>>>>
>>>>On the other hand, the application functional user that
>>>>the ASP application uses to access MSSQL via ODBC; this
>>>>user can only:
>>>>- insert/edit data
>>>>- execute the Stored Procedures.
>>>>
>>>>Lets call the developer user devuser
>>>>Lets call the app user appuser
>>>>
>>>>When ever any from my team of devuser's create an SP, the
>>>>owner asigned is devuser of course. But when the appuser
>>>>needs to execute it from the ASP application (DLL), it is
>>>>not found.
>>>>Well, since I created this special costume rol with
>>>>execute permissions for the SPs, the procedure can be
>>>>accesed adding the user before the SP name
>>>>like: 'devuser.sp_mysp'.
>>>>
>>>>Same thing for creation: When ever a 'create procedure'
>>>>statement is done, I can add the owner like 'create
>>>>procedure dbo.sp_mysp. But only if devuser is from the
>>>>db_owner rol.
>>>>
>>>>If the dbo is the owner of the sp, the sp can be directily
>>>>called. This would be great but devuser is not db_owner.
>>>>
>>>>So that this is the problem: I cannot give the devuser
>>>>db_owner rol because I don't want them to edit table
>>>>properties/PK/FK, add tables, and stuff.
>>>>
>>>>Please advice.
>>>>Thank you very much
>>>>
>>>>Ricardo Madariaga
>>>>rmadariaga@omegacl.cl
>>>>
>>>>
>>>
>
>

Quantcast