Disable access to all users other than application users in a SQL Server 2005 database.


  • Hi all,

    I have a peculiar thing to achive in SQL Server 2005.

    I've created a DB with set of tables, stored procedures and functions. Ofcourse all these are in a differernt scema other than "dbo".

    Wherever I create this DB (in production), how can I restrict access to all logins (Windows NT and SQL logins) other than desired ones? Atleast they should not be able to view data/structure for tables, and definition for views/SPs/Functions. At the same time I want no one other than specific application user to take a backup of the DB or restore it any where else ( I mean any other box) to freak out with the structure.

    Also, If I create procedures with "WITH ENCRYPTION" statement, can dbo user open the code? if so, how to disable the same?

    How can I achive this?


    Thanks in advance for your sincere effort.


    Jagadeesh Aithal.

    Jagadeesh Aithal
    Sunday, March 21, 2010 12:09 PM


All replies