The series so far:
- Introduction to SQL Server Security — Part 1
- Introduction to SQL Server Security — Part 2
- Introduction to SQL Server Security — Part 3
- Introduction to SQL Server Security — Part 4
- Introduction to SQL Server Security — Part 5
- Introduction to SQL Server Security — Part 6
In the previous articles in this series, I introduced you to SQL Server security principals and the part they play in authentication and authorization. At their most basic, principals are server and database entities that can request access to SQL Server resources. The most common principals are server logins, server roles, database users, and database roles.
SQL Server provides a number of built-in principals that are added automatically when you install a SQL Server instance or create a database. In some cases, it’s not always clear when and how to use these principals. For example, SQL Server automatically adds the public
server and database roles. Unlike other fixed roles, you can modify their permissions. However, this can impact common database operations, so you must proceed cautiously if changing the default configuration.
In this article, I dig into some of the more confusing built-in principals to help you better understand how they fit into the larger authentication picture. I created the examples for this article on an instance of SQL Server 2017, but much of the information applies to SQL Server editions going back to 2014 or earlier. Regardless of which edition you’re using, you should understand how these built-in principals work before enabling them or modifying them in your production environments.
The sa Login
The sa
login, short for system administrator, is one of the riskiest server-level principals in SQL Server. It’s automatically added as a member of the sysadmin
fixed server role and, as such, has all permissions on that instance and can perform any activity. If the login were hacked, the attacker could do unlimited damage.
You cannot drop the sa
login, but you can disable it. If you select Windows Authentication when installing SQL Server, the database engine assigns a random password to the account and automatically disables it. If you then switch to SQL Server Authentication, the login remains disabled, and you must manually enable it.
If you select SQL Server Authentication during installation, the account will be enabled, but you must provide a password, so make sure it’s a strong one. Even if the login is enabled, you should avoid using it for your applications. In fact, unless a connecting system absolutely requires the sa
login, it’s best that the account remains disabled.
You can verify whether the sa
login is disabled by querying the sys.server_principals
system view, using a SELECT
statement similar to the following:
1 2 3 4 5 |
USE master; GO SELECT principal_id, type_desc, is_disabled FROM sys.server_principals WHERE name = 'sa'; |
If the login is disabled, the is_disabled
value will be 1
, as shown in Figure 1.
You cannot remove the sa
login from the sysadmin
server role, but you can verify its membership:
1 2 3 4 5 6 7 |
SELECT member.name FROM sys.server_role_members rm JOIN sys.server_principals role ON rm.role_principal_id = role.principal_id JOIN sys.server_principals member ON rm.member_principal_id = member.principal_id WHERE role.name = 'sysadmin'; |
The statement uses the sys.server_role
system view and sys.server_principals
system view to return the members of the sysadmin
role, as shown in Figure 2.
If you find yourself in a situation that requires the sa
login, you must enable the account before it can be used. To do so, run an ALTER
LOGIN
statement that sets the ENABLE
property and then run the statement again to assign a password to the login, as shown in the following example:
1 2 3 4 5 6 |
USE master; GO ALTER LOGIN sa ENABLE; GO ALTER LOGIN sa WITH PASSWORD = 'tempPW@56789'; GO |
Of course, if you were to enable the login, you would need to assign a much stronger password than the one shown here. Moreover, if you’re just trying all this out, be sure to perform your modifications in a non-production environment.
You can test the sa
login in SQL Server Management Studio (SSMS) by launching a new query and changing the connection. The following steps describe how to change that connection:
- In SSMS, launch a new query.
- On the new query tab, right-click a blank area in the query editor, point to Connection and click Change Connection.
- In the Connect to Database Engine dialog box, select SQL Server Connection from the Authentication drop-down list. The form will be updated to include login and password text boxes.
- Type
sa
for the login and the password you provided when you enabled the login, and then click Connect.
After you’ve logged in as sa
, try running a query such as the following:
1 |
SELECT * FROM sys.server_principals; |
The query should run with no problem because the sa
login is permitted to do anything on the server. On my test system, the SELECT
statement returns 32 rows.
After you’ve verified the login, run the following ALTER
LOGIN
statement to disable it:
1 2 |
ALTER LOGIN sa DISABLE; GO |
Next, launch a new query and again try connecting with the sa
login, following the same steps outlined above. This time, you should receive an error similar to the one shown in Figure 3.
Whenever possible, you should ensure that the sa
login remains disabled. Also, consider renaming the login to provide another line of defense. Because the login is so well known, it’s a frequent target for cybercriminals who have lots of tools at their disposal for cracking passwords. Once they’ve gotten into your databases, you might not be able to stop them until it’s too late.
Certificate-Based Logins
When poking around SQL Server, you’re likely to notice a set of server logins whose names start and end with double hash marks, as in ##MS_PolicySigningCertificate##
. The logins are certificate-mapped accounts used by the database engine for internal purposes. You should not delete these or mess with them in any way.
To retrieve a list of certificate-based logins, run the following query:
1 2 3 4 |
SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE name LIKE '##%' ORDER BY name; |
Figure 4 shows the results that the statement returns on my system.
Notice that two of the logins are marked as disabled and their type listed as SQL_LOGIN
, rather than CERTIFICATE_MAPPED_LOGIN
. Despite these differences, Microsoft documentation specifically states that these two are also considered to be certificate-mapped logins.
You can view the certificates associated with the enabled certificate-based logins by running the following SELECT
statement:
1 2 3 |
SELECT name FROM sys.certificates WHERE name LIKE '##%' ORDER BY name; |
On my system, the statement returns the results shown in Figure 5, confirming that there’s a certificate for each enabled login.
For the most part, you don’t have to worry about the logins, unless you’re involved in an audit or performing a security scan, in which case you might need to explain to the powers-that-be why these logins are showing up in your reports.
The public Server and Database Roles
Each SQL Server instance contains the public
fixed server role, and each database (including system databases) contains the public
fixed database role. All logins belong to the public
server role, and all database users belong to the public
database role. You cannot drop either role, and you cannot add members to or remove members from either role.
The database engine assigns a set of permissions to the roles by default. Logins inherit all permissions granted to the public
server role unless a login has been specifically granted or denied those permissions. The same goes for the public
database role. Users inherit all permissions unless they’ve been specifically granted or denied permissions.
To view the permissions assigned to the public
server role, run the following SELECT
statement:
1 2 3 4 5 6 7 8 9 10 11 |
SELECT pm.state_desc, pm.permission_name, pm.class_desc, pm.major_id, ep.name FROM sys.server_permissions pm JOIN sys.server_principals pr ON pm.grantee_principal_id = pr.principal_id LEFT JOIN sys.endpoints ep ON pm.major_id = ep.endpoint_id WHERE pr.name = 'public'; |
The statement joins the sys.server_permissions
, sys.server_principals
, and sys.endpoints
system views to retrieve the relevant information. On my system, I retained the default permissions, which are shown in Figure 6
You can take a similar approach to retrieve the permissions assigned to the public
database role:
1 2 3 4 5 6 7 8 9 10 11 |
USE ImportSales1; GO SELECT pm.state_desc, pm.permission_name, pm.class_desc, pm.major_id, OBJECT_NAME(pm.major_id) obj_name FROM sys.database_permissions pm JOIN sys.database_principals pr ON pm.grantee_principal_id = pr.principal_id WHERE pr.name = 'public'; |
In this case, the public
database role is specific to the ImportSales1
database, which was created as part of the examples in the previous article in this series. The database is a simple, contained database that includes only the Sales
schema and the Customers
table. You can refer back to that article for details about the database, or you can run this statement against another database.
Figure 7 shows part of the results returned by the SELECT
statement on my system. As with the public
server role, I made no modifications to the public
database role. By default, the role has been granted 176 permissions, the majority of which are the SELECT
permission granted to system views.
If you were to run the same query against the master
database, it would return a much larger result set (2,254 rows on my system). In this case, the results would also include the EXECUTE
permission, granted on system functions and stored procedures.
One way to check effective permissions without writing scripts yourself is to use Redgate’s SQL Census. It creates a report of who has access to what on your SQL Servers and makes best practice recommendations, like disabling sa accounts. It’s still in development but it’s a good starting point to check on your SQL Server permissions and undertake any necessary cleaning tasks.
The public
roles are different from other fixed server and database roles because you can grant, deny, and revoke permissions. Even so, you should avoid modifying the public
roles and instead create one or more additional roles. However, if you work someplace that insists upon using the public
roles, you should grant permissions to the roles only on securables that you want to make available to all users. In addition, you should avoid denying permissions because they can override the permissions granted to individual users or logins.
You can also revoke permissions on the public
roles, but be careful when doing so. SQL Server assigns a number of permissions to these roles by default (as you saw in the preceding examples), and many of those permissions are used for routine database operations. Revoking permissions on these roles can impact all logins or users.
The dbo Database User and Schema
Every database contains a dbo
user and dbo
schema. Although the two are related, they serve very different purposes. (The term dbo stands for database owner.)
The dbo
user is added as a member of the db_owner
fixed database role. By default, the user is granted all permissions in a database and can perform all activities within the scope of that database. You cannot limit the dbo
user or drop the user from the database.
SQL Server automatically maps the sa
login, database owner, and members of the sysadmin
server role to the dbo
user account in each database. To verify this, connect to a SQL Server instance as one of these users and query the CURRENT_USER
system function, as in the following example:
1 |
SELECT CURRENT_USER; |
The SELECT
statement should return dbo
as the current user.
The dbo
user also owns the dbo
schema, which is the default schema for all newly created databases and users, unless a different schema is specified. As with the dbo
user, you cannot drop the dbo
schema.
To verify the login name and default database associated with the dbo
user, run the following query against one of your databases:
1 2 3 4 5 |
USE ImportSales1; GO SELECT SUSER_SNAME(sid) login_name, default_schema_name FROM sys.database_principals WHERE name = 'dbo'; |
In this case, the query is specific to the ImportSales1
database created for the previous article, but you can use any database. It should return the login for your current connection, as well as the dbo
schema.
You can also verify that the dbo
user has been added to the db_owner
database role:
1 2 3 4 5 6 7 |
SELECT member.name FROM sys.database_role_members rm JOIN sys.database_principals role ON rm.role_principal_id = role.principal_id JOIN sys.database_principals member ON rm.member_principal_id = member.principal_id WHERE role.name = 'db_owner'; |
The statement should return dbo
, along with any other role members. When I ran the query on my system, I was still working within the context of the ImportSales1
database, so the query returned only dbo
, which is tied to my login.
However, look what happens with the WideWorldImporters
database, which I attached to my SQL Server instance from the backup file I downloaded from GitHub:
1 2 3 4 5 |
USE WideWorldImporters; GO SELECT SUSER_SNAME(sid) login_name, default_schema_name FROM sys.database_principals WHERE name = 'dbo'; |
On my system, the results indicate that sa
is the associated login, along with dbo
as the default schema, as shown in Figure 8.
In addition, when I retrieve the members of the db_owner
database role, the results include both the dbo
user and my login account:
1 2 3 4 5 6 7 |
SELECT member.name FROM sys.database_role_members rm JOIN sys.database_principals role ON rm.role_principal_id = role.principal_id JOIN sys.database_principals member ON rm.member_principal_id = member.principal_id WHERE role.name = 'db_owner'; |
Because the dbo
user is associated with the sa
account, my login is added as a separate member to the db_owner
database role. In this way, both my login and the sa
login have full control over the database (assuming the sa
login is enabled).
Be aware, however, you might not see the same results on your system as I see mine. It’s possible that only the dbo
user will be added to the role. It will depend on how you’ve configured your system and added the WideWorldImporters
database.
On my system, my login is considered the owner for both the ImportSales1
database and WideWorldImporters
database, even though my login is associated only with the dbo
user in the ImportSales1
database. To confirm the database owners, I ran the following query:
1 2 3 4 |
SELECT name, SUSER_SNAME(owner_sid) FROM sys.databases WHERE name = 'ImportSales1' OR name = 'WideWorldImporters'; |
The query returned my login for both databases, as shown in Figure 9.
You should also be aware of the query engine’s behavior when it comes to the dbo
schema. When you query a database object without specifying a schema, the query engine first looks in your default schema if it’s other than dbo
. If the object is not there, the query engine looks in the dbo
schema. If the object is not in that schema, an error is returned.
When you create a user, you can specify a default schema. If you do not, the dbo
schema is assumed. Users with dbo
as their default schema do not inherit the permissions granted to the dbo
user.
In addition, if you specify a default schema other than dbo
and the user is a member of the sysadmin
server role, the specified schema is ignored and the dbo
schema is used. The default schema for all members of sysadmin
is dbo
.
The guest Database User and Schema
As with dbo
, every database contains a guest
user and a guest
schema. You can use the guest
user to grant database access to logins that are not associated with user accounts in that database, a strategy that should not be implemented lightly.
Although the guest
user cannot be dropped, it is disabled by default and assigned no permissions. Microsoft recommends that you keep it that way. If enabled, logins that should not be able to get into a database will have access. Do not enable the account unless you have a compelling reason to so.
The guest
user owns the guest
schema. Like the user, the schema cannot be dropped. However, it contains no objects and has been granted no permissions. In fact, the guest
schema is seldom used, if at all.
To verify whether the guest
user is enabled, run the following query:
1 2 3 4 5 6 7 |
USE ImportSales1; GO SELECT u.hasdbaccess, p.default_schema_name FROM sys.database_principals p JOIN sys.sysusers u ON p.principal_id = u.uid WHERE p.name = 'guest'; |
The query should return a hasdbaccess
value of 0
, indicating that the user account is disabled, as shown in Figure 10. The query should also return guest
as the default schema.
If against all advice you decide to enable the guest
account, you must grant the CONNECT
permission to the user, as shown in the following example:
1 2 |
GRANT CONNECT TO guest; GO |
Granting the CONNECT
permission is all it takes to enable the guest
user. If you rerun the previous SELECT
statement, the hasdbaccess
value should now be 1
.
At any time (the sooner, the better), you can disable the guest account by revoking the CONNECT
permission:
1 2 |
REVOKE CONNECT FROM guest; GO |
To verify whether the guest
schema contains any objects, run the following query, which will normally return zero rows:
1 2 3 4 5 |
SELECT o.name FROM sys.all_objects o JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE s.name = 'guest'; |
If you were to enable the guest
user, you might want to add objects to the guest
schema specifically for that user to access, but chances are, you won’t be touching either one.
The sys Database User and Schema
Every database includes the sys
user and sys
schema. The sys
user owns the sys
schema. The user serves no other purpose. It’s associated with no logins and is disabled by default. In all likelihood, you’ll never need to interact with this user account.
The sys
schema is another story. The database engine requires the schema for internal use. You cannot modify or drop the schema. It contains a number of important system objects, such as system tables, catalog views, dynamic management views, and built-in functions. You’ve already seen several of the catalog views in action in the previous examples. The sys
schema is particularly handy for accessing SQL Server metadata.
You can view the objects in the sys
schema by running the following query:
1 2 3 4 5 6 7 8 |
USE ImportSales1; GO SELECT o.type_desc, o.name FROM sys.all_objects o JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE s.name = 'sys' ORDER BY o.type_desc, o.name; |
In this case, the SELECT
statement is specific to the ImportSales1
database. On my system, the statement returns 2,273 rows, broken into 12 object types. To view a list of the object types, run the following query:
1 2 3 4 5 6 |
SELECT distinct o.type_desc FROM sys.all_objects o JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE s.name = 'sys' ORDER BY o.type_desc |
Figure 11 shows the results I received when I ran the SELECT
statement.
You can also retrieve a list of objects based on type, as shown in the following example:
1 2 3 4 5 6 7 |
SELECT o.name FROM sys.all_objects o JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE s.name = 'sys' AND o.type_desc = 'VIEW' ORDER BY o.name; |
The statement returns only the objects of type VIEW
. Figure 12 shows part of the results I received. There were 473 rows in all.
For more information about catalog views and dynamic management views, check out my Simple Talk article SQL Server System Views: The Basics.
The INFORMATION_SCHEMA Database User and Schema
Like sys
, every database also includes the INFORMATION_SCHEMA
user and INFORMATION_SCHEMA
schema. Again, the user is there only to support the schema. You cannot drop the user, but it is disabled by default.
Unlike sys
, the INFORMATION_SCHEMA
schema contains only a small number of views and no other object types. You can confirm this by running the following SELECT
statement:
1 2 3 4 5 6 |
SELECT o.name FROM sys.all_objects o JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE s.name = 'INFORMATION_SCHEMA' ORDER BY o.name; |
Figure 13 shows part of the results that I received. In all, there are 21 INFORMATION_SCHEMA
views for the ImportSales1
database.
For more information about INFORMATION_SCHEMA
views, refer to the same Simple Talk article, SQL Server System Views: The Basics.
SQL Server’s Odd Collection of Predefined Principals
When working with SQL Server and its databases, it’s important that you understand how the built-in principals work, especially the ones I’ve covered here. For the most part, SQL Server attempts to configure these principals in a way that best protects your data, such as disabling the sa
server login or guest
database user, but this doesn’t prevent you from taking steps that can disrupt operations or, worse still, open your data up to security risks. The better you understand how to work with the server and database principals, the better you can protect your SQL Server instance and its databases.
Load comments