How to make database Secure?????? RRS feed

  • Question

  • Hi,,,
            We are developing a standalone application....We are attaching the database through setup in sqlserver 2005

    Now i want to make my database secure....i mean to say to i don't want the user to view the table's value or any stored procedure ...and the user should not be able to delete anything like table or stored proceudre........

    Is it possible???? How to implement that????
    Tuesday, December 16, 2008 5:48 AM

All replies

  • Hi,

    When you create a database user it is not going to have those permissions unless you specifically grant them to it. So your application should not rely on some überuser such as 'sa', rather you need to create a login in and then create a database user for this login, that has just the permissions that you want your enduser to have.


    Elisabeth Rédei | www.sqlserverland.com | http://www.linkedin.com/elisabethredei
    Tuesday, December 16, 2008 7:31 AM
  • i am sorry to say but i think you have misunderstood my question...

    when my database is attached while installing the setup.exe in my client machine then i want to make my database as secure by not lettin user modify or delete or tables or stored procedure by user accessing the sql express installed on the client machine...

    I hope i am little clear now....
    Tuesday, December 16, 2008 9:43 AM
  • Hi,

    Yes maybe I did but then I am afraid I still don't understand. I would assume you use one user to do the setup but then your "end user" will use another login and be mapped to some other user in your database?

    Or who/what is this "user"?

    Elisabeth Rédei | www.sqlserverland.com | http://www.linkedin.com/elisabethredei
    • Marked as answer by abhi0410 Thursday, December 18, 2008 5:25 AM
    • Unmarked as answer by abhi0410 Thursday, December 18, 2008 5:25 AM
    Tuesday, December 16, 2008 1:37 PM
  • Hi,

    Unless your Database is encrypted, I don't think you can stop an admin on the client machine from making changes in the database.

    Admin on the client machine is by default sysadmin on the SQL Server and hence will have full control over the database. Either you trust the Client machine admin or encrypt your database and provide the decrytion key to only the trusted users.
    - Sumesh
    Tuesday, December 16, 2008 1:40 PM
  • How to encrypt the database then?????
    Thursday, December 18, 2008 5:25 AM
  • Hi Elisabeth Redei
                      i mean end user and not the person who is doing the installation... i am extremely sorry to use the wrong word...
    Thursday, December 18, 2008 5:27 AM
  • Hi,

    You cannot encrypt the entire database with all it's objects in one go but you can create your stored procedures, functions  WITH ENCRYPTION.

    You could encrypt the files with EFS but that will possibly slow down your system considerably.
    You can remove the BUILTIN\Administrators login to prevent windows administrators from gaining sysadmin access to the SQL Server.

    Go through Securing Your SQL Server 2005 Express Edition Server for some general ideas about security.

    Elisabeth Rédei | www.sqlserverland.com | MCITP, MCT | http://www.linkedin.com/elisabethredei
    Thursday, December 18, 2008 7:21 AM
  • Hey;

    i think the best way to do that is to use DLL triggers, on database level to prevent any update on any database object.

    Yaseen Rahhal

    Monday, December 22, 2008 4:06 PM
  • This question has come up many times before. For example, see:


    You are basically looking for DRM. SQL Server doesn't provide a DRM solution. The WITH ENCRYPTION clause for procedures, functions, etc, is obsolete and you should avoid using it because it can be easily circumvented.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, December 22, 2008 11:57 PM