SQL Server grand Server level permissions and database level permissions to all new users, which is a security issue. In this post I will present some of the permissions which any new login gets and how we can revoke or deny these permissions. This post based on the question asked in the MSDN forum and presents some theoretical options. Whether you should do it in production is another discussion...

✔ For more information about the permissions Granted to All Users By Default, you can check Brian Kelley post here.

What Next?

To clarify!!!
EVERYTHING THAT I WRITE FROM THIS POINT FOREWORD DESCRIBE WHAT CAN BE DONE AND NOT WHAT YOU SHOULD DO.
For Most cases this is highly not recommended!

What can be done?!?

Option 1: You can REVOKE or DENY permissions from the "public" role or the guest USER in the master and in the tempdb databases.

Option 2: A preferred option is to (1) for each LOGIN create new user in the databases master and temptdb, (2) create new database role in these databases, (3) add add the users to those roles, (4) add DENY permissions to the new role.

More information:

Note: All these actions you can execute inside the DDL TRIGGER above, so it will be executed automatically on any new LOGIN.

Note: For most cases I will NOT recommend to change permissions for public or guest. Instead you can create roles and assign those to the USER/LOGIN.

Option: For each LOGIN you can create a USER in the master and in the tempdb, so the clients will not use the default "guest". The "public" policy will still be enforced on them by default.

Note! REVOKE and 

Example/Demo Option 1

>> You can "REVOKE VIEW ANY DATABASE TO public". This will make it harder to watch which databases you have in the system and what are the databases properties using "SELECT * FROM sys.databases". In this case only the master and temptdb returns in the query.

>> You can "REVOKE VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO public".

>> You can "REVOKE VIEW ANY COLUMN MASTER KEY DEFINITION TO public".

>> You can REVOKE SELECT on specific sys tables/views like for example (IMPORTANT! this should be done for each database meaning on master and on tempdb separately)

• REVOKE SELECT on sys.databases TO public
• REVOKE SELECT on sys.all_objects TO public
• and so on....

You can also DNY permissions to the role public.

Note! don't make mistake, DNY and REVOKE are not the same. DNY adds/changes security permission, while REVOKE remove the security permission. REVOKE can be used to remove GRANT permissions or DNY permissions for example.

Example/Demo Option 1

-- do the same on the tempdb database
USE [master]
GO
CREATE ROLE [RonenDatabaseRole01]
GO
ALTER ROLE [RonenDatabaseRole01]
-- create a USER for each LOGIN in the master and in the tempdb
-- add all your clients' users to the role here
ADD MEMBER [TestSecurityUser1]
GO
-- Add DNY permissions to the new ROLE for all entities you want. For example:
-- DNY SELECT on system tables
DENY SELECT ON [sys].[columns] TO [RonenDatabaseRole01]
DENY SELECT ON [sys].[all_views] TO [RonenDatabaseRole01]
DENY SELECT ON [sys].[all_objects] TO [RonenDatabaseRole01]
DENY SELECT ON [sys].[databases] TO [RonenDatabaseRole01]
DENY SELECT ON [sys].[all_columns] TO [RonenDatabaseRole01]
DENY SELECT ON [sys].[all_objects] TO [RonenDatabaseRole01]
-- You can DNY on SCHEMA or other entities
DENY SELECT ON SCHEMA :: dbo TO [RonenDatabaseRole01]
GO

Conclusions: I totally agree that this is a security breach. In fact I showed how to use this information from the master database and the ability to create temp tables in order to crack the SQL Server security features. I wrote an article on the TechNet Wiki, and I published posts in my blog, and I lecture about this topic in English and in Hebrew in several User Groups and events. You can find the recording online, or the blog, or the article.

With That being said, I do not recommend to change the permissions of the guest USER or the public ROLE.

 

Off-Topic Note: by the way, in Azure Database the guest does not have permissions to CONNECT master, since this is not a real master but a logical server. This is more secure since you must have a USER in the master. If you create new LOGIN without a USER then you will get the error "Cannot open user default database. Login failed." since the master is the default database but the LOGIN does not have USER in this database.

You can execute the following query on premises and on azure when you connect to the master as sysadmin, and see the difference

SELECT dpr.name, dpr.type_desc, dpe.permission_name, dpe.state_desc, dpe.type, dpe.state_desc
FROM sys.database_principals dpr LEFT JOIN sys.database_permissions dpe
ON grantee_principal_id = principal_id
where name = 'guest'
GO

 

✅ It is HIGHLY recommended to read my post about: CREATE DDL TRIGGER ON CREATE_LOGIN to configure default parameters for any new LOGIN. The information in that post is directly related and can help you secure your database and do the actions we speak here, automatically on any USER/LOGIN.

👀Do you think this is all?

Actually this is only a test of the problematic default behavior of permissions.

This post does not meant to help people to crack the database but to secure (hack) the database and understand what should be done.

I hope this was useful 😀