-2

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.

  • 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 Jun 4 '15 at 4:41
6

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 EXEC (@var).

3

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.

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