SQL Server Permissions Granted to All Users By Default

Problem

I’m trying to secure a SQL Server, but the problem I’m running into is there are a lot of permissions and I don’t know which ones are set by default and which aren’t. As a result, I don’t know what I can remove. How can I obtain this information?

Solution

In order to determine the default SQL Server permissions for a new user, we’ll have to look at two levels:

• Server level
• Database level

Even within the database level, we’ll need to look at all the system databases and one pristine database. We’ll have to look at this from two different directions:

• The public role
• A named login/user we’ll create just for this purpose.

Server Level Permissions in SQL Server

Let’s start with the public role, as that’s the easiest to query. There’s only a small set of default permissions at the server level, whether we are talking about the public role or a named user. First, let’s look at what permissions the public role has at the server level:

SELECT sp.state_desc, sp.permission_name, sp.class_desc, sp.major_id, sp.minor_id, e.name
FROM sys.server_permissions AS sp
  JOIN sys.server_principals AS l
    ON sp.grantee_principal_id = l.principal_id
  LEFT JOIN sys.endpoints AS e
    ON sp.major_id = e.endpoint_id
WHERE l.name = 'public';

The results are small:

As you can see, public has the ability to view any database (see that the database exists) as well as connect to the various endpoints, but nothing else. If you see any other permissions, they’ve been added. Do note, that the VIEW ANY DATABASE permissions doesn’t give a person the ability to enter a database. It just lets them the see the name of all databases on that SQL Server.

So what about a named user? Let’s create a login and check to see what permissions are added specifically because of the login:

CREATE LOGIN TestLoginPerms WITH PASSWORD = '!S0meStr0ngP4ssw0rd!';
GO 
SELECT sp.state_desc, sp.permission_name, sp.class_desc, sp.major_id, sp.minor_id
FROM sys.server_permissions AS sp
  JOIN sys.server_principals AS l
    ON sp.grantee_principal_id = l.principal_id
WHERE l.name = 'TestLoginPerms';

Not surprisingly, there is only one permission, the ability to connect to SQL Server:

With only one entry, that tells us anything else beyond connecting to the SQL Server has to be added after the fact.

SQL Server Database Level Permissions for the Master Database

Once we move on from server permissions, we have a problem. There are a lot of objects for which permissions are granted on the system databases. And depending on what you may have installed on your SQL Server that’s legitimate, your count may differ from mine. However, let’s see how do go about collecting the information.

Because guest is enabled for the master database, anyone who can connect to SQL Server has the ability to enter the master database. They connect as that guest user, meaning we need to query what public has access to:

USE master;
GO 
SELECT sp.state_desc, sp.permission_name, SCHEMA_NAME(o.schema_id) AS 'Schema', o.name
FROM sys.database_permissions sp
  LEFT JOIN sys.all_objects o
    ON sp.major_id = o.object_id
  JOIN sys.database_principals u
    ON sp.grantee_principal_id = u.principal_id
WHERE u.name = 'public'
  AND o.name IS NOT NULL
ORDER BY o.name

If you’ll note, I’m querying a catalog view, sys.all_objects, which you don’t typically see in administrative queries. The difference between sys.objects and sys.all_objects is that sys.all_objects also contains references to the system objects. Since we’re trying to get all permissions, we need to reference all objects, including system ones.

Now with that said, there are still a lot of NULLs with the LEFT JOIN when trying to map permissions against sys.all_objects. I could have used the LEFT JOIN, but in case you want to see those NULL values, you can simply comment out the filter against the NULLs. Even with the filtering out of the NULLs, my query generated nearly 2000 rows. If you’re trying to determine what is set up by default, you’ll need to set up a pristine server of the same version as the one you’re trying to clean up with the same configuration. Take it through all the steps your organization performs to setup a server. For instance, if you include the stored procedures that are included to extract logins (such as sp_help_revlogin), make sure those are deployed accordingly.

SQL Server Database Level Permissions for msdb and tempdb

Except for changing the database we’re running against, we’ll run the same T-SQL query against each of these three databases as we did against master. The model database doesn’t grant access to guest, as it’s copied for every user database when created and for tempdb at SQL Server start. So we’ll look at model when we consider a standard user database. As for results, here’s what my queries generated:

msdb:

tempdb:

When I look at the number of rows, I see 174 rows for msdb and 139 for tempdb. The 174 rows for msdb makes sense since msdb contains objects to handle SQL Server Agent jobs, track backups, etc. Again, your actual numbers may vary depending on what you have installed. Do note, if you’ve installed additional objects and permissions in model, your tempdb numbers should match the numbers for model.

SQL Server Permissions for User Defined Databases

That just leaves a generic user database. We can query model for public to see what public is normally granted. That’s because model is what is copied to generate a new user database. Therefore, we’ll execute the same query for public as we have for master, msdb, and tempdb, just on model.

And like with tempdb, I see 139 rows. This makes sense given that tempdb is created from model. One caution: if your organization makes changes to model so all new user databases reflect those changes, you’ll need to set up the pristine SQL Server if you want to identify what was added.

This still leaves a specific user. We’ll re-use the login we created to see the specific permissions against the server:

CREATE DATABASE TestDB;
GO 
USE TestDB;
GO 
CREATE USER TestLoginPerms;
GO 
SELECT sp.state_desc, sp.permission_name, SCHEMA_NAME(o.schema_id) AS 'Schema', o.name
FROM sys.database_permissions sp
  LEFT JOIN sys.all_objects o
    ON sp.major_id = o.object_id
  JOIN sys.database_principals u
    ON sp.grantee_principal_id = u.principal_id
WHERE u.name = 'TestLoginPerms'
  AND o.name IS NOT NULL
ORDER BY o.name;

And when we query, we get the following results:

Which means that if you have any additional permissions for a named user (or a created role), they were added. They weren’t part of what SQL Server did when the user was created.

Roles Other Than Public

What about other roles? Can permissions be assigned to them? Obviously, if they are user-defined roles, they’ve been created and aren’t the default. So that leaves the fixed roles. Let’s test that:

USE TestDB;
GO 
CREATE TABLE testTable (TestID INT);
GO 
GRANT SELECT ON dbo.testTable TO db_ddladmin;
GO 

We’ll get an error:

Therefore, we don’t have to worry about the roles other than public.

Next Steps

• Learn how to audit server level permissions more in depth.
• Understand the various database permissions that can be granted.
• Read up on implicit permissions due to particular SQL Server database roles.
• Prepare to extract database permissions for disaster recovery.