locked
Can I protect my database from all other users RRS feed

  • Question

  • Hi,

    I am new to Database and SQL Server. I have a question about Security in SQL Server. I have a a database in a local server. After googling, I found out that I cannot set password for my database. So, is there any solution to protect my database from all other users, including sa user?

    Thank you in advance

    Tuesday, May 10, 2016 12:26 PM

Answers

  • No.  sysadmin users have unrestricted access to all databases on the server.

    You can control non-sysadmin user rights at a very granular level.

    I would suggest you start here:

    https://msdn.microsoft.com/en-us/library/bb669078(v=vs.110).aspx


    • Edited by Tom Phillips Tuesday, May 10, 2016 12:36 PM
    • Marked as answer by Khac Minh Wednesday, May 11, 2016 2:38 AM
    Tuesday, May 10, 2016 12:36 PM
  • SysAdmin users will always have unrestricted access to anything within a SQL Server Instance, they need this access to create/drop databases/logins etc; carry out additional tasks within a database e.g. creating new roles, assigning objects to a role etc, set up SQL Agent jobs, perform backups, set up Maintenance Plans etc.

    I would first of all ask yourself why you need to restrict access to all other users; if this is paramount then you wil need a separate SQL Server Instance with just your database in it and you set yourself up as the System Administrator.


    Please click "Mark As Answer" if my post helped. Tony C.

    • Marked as answer by Khac Minh Wednesday, May 11, 2016 2:38 AM
    Tuesday, May 10, 2016 1:07 PM

All replies

  • No.  sysadmin users have unrestricted access to all databases on the server.

    You can control non-sysadmin user rights at a very granular level.

    I would suggest you start here:

    https://msdn.microsoft.com/en-us/library/bb669078(v=vs.110).aspx


    • Edited by Tom Phillips Tuesday, May 10, 2016 12:36 PM
    • Marked as answer by Khac Minh Wednesday, May 11, 2016 2:38 AM
    Tuesday, May 10, 2016 12:36 PM
  • Hello Khac Minh,

    You can create logins to enter in SQL Server and assign required server roles to it, create database user and assign database roles to that user and map to the logins for database/Server security. 

    below is the link for your reference. 

    Managing Logins, Users, and Schemas How-to Topics

    Please mark me as answer if my post helps you.

    Regards

    ChetanV

    Tuesday, May 10, 2016 12:57 PM
  • SysAdmin users will always have unrestricted access to anything within a SQL Server Instance, they need this access to create/drop databases/logins etc; carry out additional tasks within a database e.g. creating new roles, assigning objects to a role etc, set up SQL Agent jobs, perform backups, set up Maintenance Plans etc.

    I would first of all ask yourself why you need to restrict access to all other users; if this is paramount then you wil need a separate SQL Server Instance with just your database in it and you set yourself up as the System Administrator.


    Please click "Mark As Answer" if my post helped. Tony C.

    • Marked as answer by Khac Minh Wednesday, May 11, 2016 2:38 AM
    Tuesday, May 10, 2016 1:07 PM