locked
Database security issue RRS feed

  • Question

  • We have different vendors trying to access our database. For eg: Database Server A and the vendor company is Company A, when this vendor Company A is trying to access Database Server A they should only be able access the information pertaining to their business. This is just an example we have many servers and many vendors. How would I set this up.

    Thanks in advance......................

    Tuesday, May 3, 2011 5:18 AM

Answers

All replies

  • how do the vendor company accesses SQL Server, which logins they use. Do the different companies have different logins?

    I am little confused on Database Server A , is that a database or an instance? If only company A accesses the Instance A then you can give only permissions to access company A using (logins) to access the Instance. If all the companies have databases in the same instance, then you need to give permissions for the company accounts corresponding to databases by giving permissions in database level.

    I hope it is clear.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
    Tuesday, May 3, 2011 5:24 AM
  • There are many vendor companies trying to connect to Database Server A. Its name of the server and there are many DB objects in different Databases that these vendor companies try to access.

    Thanks very much for your reply......

    Tuesday, May 3, 2011 5:52 AM
  • create SQL login for each vendor and give them permission only in their database.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Tuesday, May 3, 2011 6:00 AM
  • well thanks for the reply. but the problem is they access information access across multiple databases and they should be given access only to their information. Is there an option in SQL Server to provide row level security.

    Thanks in advance...........

    Tuesday, May 3, 2011 6:37 AM
  • Refer this whitepaper
    http://msdn.microsoft.com/en-us/library/cc966395.aspx
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Tuesday, May 3, 2011 6:42 AM
  • Your way to go....

    http://www.sommarskog.se/grantperm.html


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, May 3, 2011 6:50 AM
  • well thanks for your quick response. I guess implementing horizontal scalability would be a safe and optimum solution. If you could provide leads or white paper on how to implement that would be really helpful.

     

    Tuesday, May 3, 2011 7:02 AM
  • well thanks for your quick response. I guess implementing horizontal scalability would be a safe and optimum solution. If you could provide leads or white paper on how to implement that would be really helpful.

     


    my last response has a whitepaper
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Tuesday, May 3, 2011 7:12 AM
  • Hi, I wanted to have white paper on horizontal scalability..........thanks 
    Tuesday, May 3, 2011 7:26 AM
  • Hmm.  Well, I don't have a whitepaper, as such, but people have given pointers.  Note:  Scaling out (horizontally) a database tier is harder than scaling out a web site, but it can be done.  People have things to say:

    Don Jones:
    http://searchsqlserver.techtarget.com/tip/Scaling-up-vs-scaling-out-with-SQL-Server-2008
    http://www.snip.gob.ni/Xdc/SQL/DGSOSSFinal.pdf - 156 page "Definitive Guide to Scaling Out" (could be a whitepaper)

    Three articles by Tim Chapman:
    http://www.techrepublic.com/article/scaling-your-sql-server-system/6167716
    http://www.techrepublic.com/article/two-options-for-scaling-out-your-sql-server-system/6170401
    http://www.techrepublic.com/article/scaling-out-with-distributed-partitioned-views-in-sql-server-2005/6172535

    Et cetera.

    From your notes above I could not understand the overall problem.  Since scaling out is using more servers the question is: How will you use each server?  That is where the design and implementation issues focus.  This may range from give each company (a) its own SQL Server instance, (b) its own databases on an instance, ... (z) all companies on one scaled out platform, implementing row level security, partitioning, etc.

    How far do you want to go?  What are your drivers?  How much money can you spend / should you save? 

    RLF 


    • Proposed as answer by WeiLin Qiao Thursday, May 5, 2011 9:08 AM
    • Marked as answer by WeiLin Qiao Wednesday, May 11, 2011 4:34 AM
    Tuesday, May 3, 2011 1:16 PM