mssqltips logo

SQL Server Permissions Granted to All Users By Default

By:   |   Updated: 2016-03-28   |   Comments (3)   |   Related: More > Security

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:

Public permissions at the server level

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:

Named User Server Level Permissions

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.

Public permissions in the master database

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:

Public permissions for msdb database

tempdb:

Public permissions for tempdb database

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.

public permissions against a user database

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:

Named user permissions for all user databases

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:

Trying to grant permission to db_ddladmin

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

Next Steps


Last Updated: 2016-03-28


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, October 25, 2018 - 7:00:29 PM - Dmitry Back To Top

Thanks a lot for those queries. I have a database where someone has granted the public role all permissions to all tables, and I was totally bewildered why adding a user to the database allowed them to do anything without being a member of any role, until I ran the above query for "public". There doesn't seem to be a way to see that anywhere in the Studio.

 


Friday, May 06, 2016 - 12:55:20 PM - K. Brian Kelley Back To Top

 

Any changes to the default permissions would leave the SQL Server in an unsupported state.

Also, keep in mind that even with permissions, a user can only see through the sys views the objects he or she has access to. So even though a user might have access to sys.tables, the user would only see the tables the user account could access. If that's 2 tables out of 250 in the database, the user would only see 2 when querying sys.tables. 


Friday, May 06, 2016 - 11:23:28 AM - Leiah Back To Top

 Should the permissions granted to public on the sys schema be revoked? Would that cause any problems with the database?

I'm thinking that the metadata that it allows users to see can even function as some sort of reconnosaince tool to determine where are the "crown jewels". I would think if specific users need these permissions, then they should be added to a user-defined role, grant users access to that role, and then remove them from public. Is my statement correct?

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools