locked
Can revoke permissions to all users? RRS feed

  • Question

  • Hi there,

    Everyone have the permissions, I want to revoke it from anyone who doesn’t need to have it.Can we grant just the roles we need to do what we need? How to do that

    thanks


    Friday, November 18, 2016 11:46 PM

Answers

  • The starting point is that users do not have permission to anything.

    However, if you are working in an existing database, all sorts of permissions may have been granted on various level.

    Maybe the simplest is to create a new database and make sure that you are the only user with access that database.

    However, keep in mind that logins that are member of sysadmin or have the server-level permission CONTROL SERVER can access any database on the instance.

    Saturday, November 19, 2016 10:35 AM
  • Though no one automatically gets explicit access permissions to a new table, people who have permissions on the schema or database could inherit permissions on they new table. You could DENY permission to others, and that will override any grants. If the permission was granted widely (for example SELECT permission on the database or schema to the public role, then it would be complicated. You can't REVOKE or DENY permission to public or you would lock yourself out, too. Only admins would have access. So your first job, is to find out if people get access to your new table, and if so, how do they get it?

    And as described, it's never possible to block access to administrators, though you can make it difficult and auditable.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by chandu.muzik Wednesday, November 23, 2016 9:49 AM
    Monday, November 21, 2016 4:21 PM

All replies

  • The starting point is that users do not have permission to anything.

    However, if you are working in an existing database, all sorts of permissions may have been granted on various level.

    Maybe the simplest is to create a new database and make sure that you are the only user with access that database.

    However, keep in mind that logins that are member of sysadmin or have the server-level permission CONTROL SERVER can access any database on the instance.

    Saturday, November 19, 2016 10:35 AM
  • Though no one automatically gets explicit access permissions to a new table, people who have permissions on the schema or database could inherit permissions on they new table. You could DENY permission to others, and that will override any grants. If the permission was granted widely (for example SELECT permission on the database or schema to the public role, then it would be complicated. You can't REVOKE or DENY permission to public or you would lock yourself out, too. Only admins would have access. So your first job, is to find out if people get access to your new table, and if so, how do they get it?

    And as described, it's never possible to block access to administrators, though you can make it difficult and auditable.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by chandu.muzik Wednesday, November 23, 2016 9:49 AM
    Monday, November 21, 2016 4:21 PM
  • Hi,

    if there is an existing database with all permissions and roles were set before for sysadmin, don't we have any chance to DENY or Revoke permissions (as a user) to a particular users with various roles on different schema rather than creating a new database and giving the fresh permissions and roles? 

    please help


    Sunday, December 4, 2016 10:21 AM
  • I suggested that you should create a new database for this specific table, so that you are not tripped by other permissions granted in this database.

    It is certainly possibly to add this table and make sure that you (and dbo and sysadmin) are the only one who can access this table. However, depending on how permissions are set up, it may be a lot of work to achieve. And then on one day some grants something to some role which they shouldn't have and the table is open.

    DENY may seem alluring, but since DENY takes precedence over GRANT, it can be difficult too. If you DENY permission to some general group of users, there is a fair chance that you are memeber of that group too, and you will also be denied access.

    I like to add that my answer was based on a fairly vague question of yours. It's difficult to give an accurate answer when knowing very little about the actual situation.

    Sunday, December 4, 2016 10:59 AM
  • Thank you Erland. I am clear now.
    Sunday, December 4, 2016 1:16 PM