How to make SQL Server Database Not Viewable ? RSS

• • Reply
  

  nagarjunared...

  Member

  25 Points

  57 Posts

  How to make SQL Server Database Not Viewable ?

  Nov 16, 2012 10:12 AM|LINK

  Hi,

  I have SQL Server Database which is having lot of usefull information which I dont want to view by anybody. How Can I do that...?

  But that Database should be accesible to ASP.NET application.

  OR 

  In That Database they are 6 Important Tables are there. How to make those 6 Tables not viewable..?

  Why I am wondering because I need to give entire database to the Clients.  Even Clients dont want to view all those important 6 Tables in that Database...

  Please give me Some suggestions

  Regards Venkat

• • Reply
  

  manjuby

  Participant

  1131 Points

  251 Posts

  Re: How to make SQL Server Database Not Viewable ?

  Nov 16, 2012 11:48 AM|LINK

  I think u can take use of Security tab located in object explorer..

  1) First create users for the database

  2) Right click->properties->user mapping : here u can set the user credentials..

    

  Plz click Answer..if it suits your need
• • Reply
  

  nagarjunared...

  Member

  25 Points

  57 Posts

  Re: How to make SQL Server Database Not Viewable ?

  Nov 16, 2012 12:13 PM|LINK

  I am wishing that No body can View that  data in that Database Tables or As I said in my post there are 6 important tabels are there in the table I dont want any body View THE DATA IN THAT TABLES...

  Can you please explain above scenario in step by step manner

  Thanks in Advance

• • Reply
  

  Chen Yu - MS...

  All-Star

  21569 Points

  2493 Posts

  Microsoft

  Re: How to make SQL Server Database Not Viewable ?

  Nov 20, 2012 09:14 AM|LINK

  Hi,

  @manjuby means you could use User Account permission to limit those people who you don't want them view the detail imformation about tables.  You could create a new login with proper authentication, in User Mapping, choose the database and grant it proper permisson.

  You could reference on this link: http://msdn.microsoft.com/en-us/library/aa337562.aspx 

  Best Regards,

  Please mark the replies as answers if they help or unmark if not.
  Feedback to us
  
  Develop and promote your apps in Windows Store
  
  
  • Marked as answer by Chen Yu - MSFT on Dec 03, 2012 01:52 AM
• • Reply
  

  manjuby

  Participant

  1131 Points

  251 Posts

  Re: How to make SQL Server Database Not Viewable ?

  Nov 20, 2012 11:00 AM|LINK

  Yes U got my point...Chen Yu - MSFT

  Thanks

  Plz click Answer..if it suits your need
• • Reply
  

  nagarjunared...

  Member

  25 Points

  57 Posts

  Re: How to make SQL Server Database Not Viewable ?

  Nov 20, 2012 12:03 PM|LINK

  Thanks... Ya I did that...AND its working too...I am very happy with that...

  What I did here .....I have created a User Granted permission to the DataBase (suppose DBSystem)....

  When he logged in He can not see all the tables in that Database.....

  Thanks and my First Quey Clarified 

  My second Qestion is

  How can we restrict that user for specified tables in that DataBase...?

  Suppose I have totally 10 tables in the database...

  I want that user can not see 6 tabels and remain 4 tables can see

  How can we do that....?

   

• • Reply
  

  manjuby

  Participant

  1131 Points

  251 Posts

  Re: How to make SQL Server Database Not Viewable ?

  Nov 20, 2012 12:10 PM|LINK

  --create a role to wrap up our permissions 
  CREATE ROLE TWOTABLEACCESS 
  GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ALL_BLOCKGROUPS TO TWOTABLEACCESS; 
  GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.GEOSTATE TO TWOTABLEACCESS; 
  
   --now test the role 
  CREATE USER ROLETESTER WITHOUT LOGIN;  
  EXEC sp_addrolemember 'TWOTABLEACCESS','ROLETESTER' 
  
   EXECUTE AS USER = 'ROLETESTER'
  
  
  to get back to norml things..
  
  REVERT;

  Plz click Answer..if it suits your need
  • Edited by manjuby on Nov 20, 2012 12:11 PM
• • Reply
  

  Chen Yu - MS...

  All-Star

  21569 Points

  2493 Posts

  Microsoft

  Re: How to make SQL Server Database Not Viewable ?

  Nov 21, 2012 02:21 AM|LINK

  Hi,

  You could use grant Table permission to do so. Please reference on below article.

  http://msdn.microsoft.com/en-us/library/ms188371.aspx 

  Best Regards,

  Please mark the replies as answers if they help or unmark if not.
  Feedback to us
  
  Develop and promote your apps in Windows Store
  
  
  • Marked as answer by Chen Yu - MSFT on Dec 03, 2012 01:52 AM
• • Reply
  

  Chen Yu - MS...

  All-Star

  21569 Points

  2493 Posts

  Microsoft

  Re: How to make SQL Server Database Not Viewable ?

  Nov 21, 2012 02:21 AM|LINK

  Hi,

  You could use grant Table permission to do so. Please reference on below article.

  http://msdn.microsoft.com/en-us/library/ms188371.aspx 

  Best Regards,

  Please mark the replies as answers if they help or unmark if not.
  Feedback to us
  
  Develop and promote your apps in Windows Store