Revoke connect permission from Public role

I am using MSSQL 2008 R2. I have a particular database that when it is restored it is being accessible from everyone from the SQL Management Studio. By using the below SQL statement I have identif开发者_运维技巧ied that the Public server role has been granted the connect permission on this database.

use db_mydb
SELECT * 
  FROM sys.database_permissions 
 WHERE grantee_principal_id = (SELECT principal_id 
                                 FROM sys.server_principals 
                                WHERE name ='public')

With the result of this query being the below

0 DATABASE 0 0 2 1 CO CONNECT G GRANT

Is there any work around to revoke this permission?

One of the correct ways would be

• Run DROP USER all users in the database
• Run DENY CONNECT TO those users

Don't mess with public

Your code is misleading you too:

• The server level public role is unrelated to the database level public role
• sys.database_permissions.grantee_principal_id refers to sys.database_principals
• For roles, there is no common column between sys.database_permissions/sys.database_principals and sys.server_principals

My solution for this was to create an empty database. Then script the tables, views and stored procuders using the Generate Scripts. And then import the data using the Import Data option. All of this can be done using only the Micosoft SQL Management Studio.