locked
Read only RRS feed

  • Question

  • Hi ,

    i wanted to share my sql server 2012 database to the other project team within the organization. at the same time i should not allow them to be a DB owner. i,e i will be sending them BAK file ,so that the database is hosted on their system.

    on restoring the database on their system they become sysadmin.

    so i wanted to stop them , by viewing the code, generate scripts of Datase funnctions, triggers, stored procedure . only

    they can view table name and their schema.like viz stored procedure name , but not the code in that.

    Eg: like creating EXE file for .net win application, they can consume , make a call to application. but restrict viewing actual code.

    In the same way, how can i restrict them in sql server  2012? 

    <button class="c-button msgReplyBtn" style="box-sizing:inherit;color:#ffffff;font-style:inherit;font-variant:inherit;font-weight:inherit;font-size:15px;line-height:1;font-family:inherit;margin:12px 0px 0px;cursor:pointer;padding:9px 12px 10px;max-width:374px;min-width:120px;background-color:#0067b8;border-width:1px;border-style:solid;border-color:transparent;white-space:nowrap;vertical-align:bottom;outline:transparent dashed 1px;transition:scale 0.2s ease-in-out;" title="Reply to this post" type="submit">Reply</button>
     
    <button class="c-button f-lightweight thdJoinBtn disabled " data-votecount="0" disabled="disabled" style="box-sizing:inherit;color:rgba(0, 0, 0, 0.2);font-style:inherit;font-variant:inherit;font-weight:inherit;font-size:15px;line-height:1;font-family:inherit;margin:12px 0px 0px;cursor:not-allowed;padding:10px 12px 11px 0px;max-width:374px;min-width:120px;background-image:initial;background-background-size:initial;background-repeat:initial;background-attachment:initial;background-origin:initial;background-clip:initial;border-width:0px;border-style:initial;border-color:initial;white-space:nowrap;vertical-align:bottom;outline:transparent dashed 1px;transition:scale 0.2s ease-in-out;" title="This is your question">I have the same question (0)</button>
     
    <button aria-expanded="false" aria-haspopup="true" class="c-action-trigger" style="box-sizing:inherit;color:#006cd8;font-style:inherit;font-variant:inherit;font-weight:inherit;font-size:24px;line-height:inherit;font-family:MWF-MDL2;margin:12px 0px 0px;overflow:hidden;cursor:pointer;padding:8px;max-width:374px;min-width:36px;border-width:1px 1px 0px;border-top-style:solid;border-right-style:solid;border-bottom-style:initial;border-left-style:solid;border-top-color:transparent;border-right-color:transparent;border-bottom-color:initial;border-left-color:transparent;background-image:initial;background- 0px;background-size:initial;background-repeat:initial;background-attachment:initial;background-origin:initial;background-clip:initial;white-space:nowrap;vertical-align:bottom;outline:transparent dashed 1px;;height:37px;"></button>
    Monday, August 7, 2017 5:21 AM

Answers

All replies

  • 1) You can encrypt  those objects but if they want it is matter of time to hack it as SQL Server doesn't truly provide a foolproof method to protect module code

    In my opinion probably a safer way (but still not perfect) is to put some or all of the procedure's business logic into the CLR (read more https://msdn.microsoft.com/en-us/library/ms190790.aspx?f=255&MSPPError=-2147217396).



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, August 7, 2017 5:44 AM
  • Hi shindagi,

     

    What will other project team do with your database? I think share a .bak file is not a good idea.

     

    Actually, many methods can control the permission on the database and stored procedures. For instance, creating some logins for them and grant limited permission to them.

     

    Best Regards,

    Teige

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 7, 2017 9:12 AM
  • Why do not you use "WITH ENCRYPTION"

    But limitaions are 

    you need to chnage your code every where

    even you cant able to see the code once you applied With encryption.

    Thanks,

    Dileep Achhala. 


    ADKR

    Monday, August 7, 2017 9:27 AM
  •  Basically the Application and SQL server Database needs to be hosted on the different system. so the the other team perform  a test on to the Application and do the verification and validation in the back end.

    Basically i wanted to restrict them to view or generate the script module.

    Eg: when Sql Server Databse  hosted onto different machine, then Access Privileges wont work. since the current user becomes the SYSADMIN.

    Let me know , if there are any other methods.

    TIA,

    Shindagi 

    Tuesday, August 8, 2017 5:42 AM
  • Hi Dileep,

    Thanks , here i donot  want to encrypt the data. only source code or Script i wanted to hide, avoid copying, generate script etc.

    Hope it is clear now.

    Tuesday, August 8, 2017 5:46 AM
  • As you probably know you cannot restrict sysadmin from doing anywhere on the server as SQL Server doesn't truly provide a foolproof method to protect module code

    See my suggestion above 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, August 8, 2017 5:47 AM
  • Hi Uri,

    thanks, much appreciated . i am convinced  by your answer but i do have JAVA application and Backend as SQL server 2012 database.

    So i am not aware of ODBC or JDBC reposond to Assembly creation.

    Eg:Define the stored procedure as a static method of a class in a language supported by the .NET Framework. For more information about how to program CLR stored procedures, see CLR Stored Procedures. Then, compile the class to build an assembly in the .NET Framework by using the appropriate language compiler.

    As suggested by you(https://msdn.microsoft.com/en-us/library/ms190790.aspx?f=255&MSPPError=-2147217396)


    Tuesday, August 8, 2017 5:55 AM
  • Hi shindagi,

    Java can also use CLR Stored Procedure via JDBC.

    Best Regards,

    Teige


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Friday, August 18, 2017 2:50 AM