How can I deny the access to a database for all the users except for the sa BY CODE? I know how to do it by using the interface in management studio, but I need to have it on the script.

  • so, you want to revoke access for everyone except a single user? and you know for certain that user is sa? You know, for certain, that sa exists on the instance(s) in question? I ask this since you presumably are looking for code that can run against multiple instances, otherwise why are you mandating code. Not that code is bad, I'm just trying to clarify your intent to prevent us from shooting you in the foot. – Max Vernon Jun 4 '15 at 1:29
  • 1
    Also, what version of sql-server ? – Max Vernon Jun 4 '15 at 1:30
  • Yes, sa exists and is for a school project and i need to run the script on a virtual machine the teacher is going to run for us, so it all has to be on code. And yes, revoke access for everyone except sa. Sql server 2014 – Drivera Jun 4 '15 at 1:31
  • If this is for a homework assignment you should probably try to determine these things through either (a) looking in the manual (RTFM), or (b) doing a google search. Asking for the answer is not really helping you learn how to do figure it out. – Max Vernon Jun 4 '15 at 1:40
  • 2
    This question needs to be more specific: are you looking to restrict access to a single database, or the whole SQL Server instance? Specifically just to the 'sa' login, or anyone with admin (CONTROL SERVER) or database owner permissions? A screenshot of what you're doing in SSMS might help clarify your question too. – Paul White 9 Jun 4 '15 at 4:41

I'm reluctant to provide this code since it could be used to incorrectly deny access to everyone, if you don't have the 'sa' password, or if the 'sa' user is disabled, or if SQL Server logins are not permitted.

The following code will generate T-SQL statements to disable the logins for every Windows User, Group, and SQL Server login that is not the 'sa' user:

FROM sys.server_principals sp
WHERE sp.principal_id > 100
    AND sp.is_disabled = 0
    AND sp.type IN (
        'U' -- Windows user
        , 'G' -- Windows Group
        , 'S' -- SQL user

In order to actually revoke access to these accounts, you will need to copy-and-paste the results into a new command window and run them via F5

I'm purposely not adding code to automate the last part since this could easily be used to lock yourself out of your SQL Server. If you wanted to automate it, you could search for dynamic SQL, and EXEC (@var).

| improve this answer | |

The following is not exactly a match for you but it might be useful. A SQL-server database can be put into single user and restricted user modes. Single user as the name suggest means only one user can connect to it. Restricted means only users in sys_admin and db_owners can connect to it.

This can be achieved by executing:



Keep in mind these scripts will not execute immediately as for example the single user one will be blocked if more than one user is connected. It will have to wait for all users to disconnect before going into SINGLE_USER mode.

To force these scripts add the WITH ROLLBACK IMMEDIATE clause.


These scripts can be used in emergencies to block access to the database so you can get in and fix things. Helped me a lot years ago when two disks in a RAID 5 went sour.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.