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.
closed as unclear what you're asking by Paul White♦, Max Vernon, RolandoMySQLDBA, Mark Storey-Smith, Colin 't Hart Jun 4 '15 at 9:00
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
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:
SELECT 'ALTER LOGIN ' + QUOTENAME(sp.name) + ' DISABLE;' 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
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:
ALTER DATABASE [YourDbName] SET SINGLE_USER; ALTER DATABASE [YourDbName] SET RESTRICTED_USER;
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.
ALTER DATABASE [YourDbName] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
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.