Why You Should Care About Users with Elevated Permissions in SQL Server

Users with Elevated Permissions in SQL Server

The security model offered by Microsoft SQL Server is highly configurable and very robust when all security best practices are followed.

Why should you care about this?

The least-privileged user account (LUA) approach is an important part of a defense-in-depth strategy against security threats.

LUA says a user should only be granted the privileges needed to do their job — no more, no less.

This is a best practice that can prevent many future problems, such as someone accidentally renaming or dropping an object.

You can learn more about SQL Server database engine permissions in Microsoft’s documentation.

How do we find users with broad permissions in SQL Server?
SysAdmin users
Run the following query to list all users who are system administrators or who have GRANT CONTROL SERVER.

USE master

GO

SELECT DISTINCT p.name AS [loginname] ,

p.type ,

p.type_desc ,

p.is_disabled,

s.sysadmin,

CONVERT(VARCHAR(10),p.create_date ,101) AS [created],

CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]

FROM sys.server_principals p

JOIN sys.syslogins s ON p.sid = s.sid

JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id

WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')

-- Logins that are not process logins

AND p.name NOT LIKE '##%'

AND (s.sysadmin = 1 OR sp.permission_name = 'CONTROL SERVER')

ORDER BY p.name

Database owners

Run the following query to list all users mapped to the db_owner role.

exec sp_msForEachDb ' use [?]

select db_name() as [database_name], r.[name] as [role], p.[name] as [member] from

sys.database_role_members m

join

sys.database_principals r on m.role_principal_id = r.principal_id

join

sys.database_principals p on m.member_principal_id = p.principal_id

where

r.name = ''db_owner'''

How do you reduce user permissions?

For the users listed above, choose a lower permission level if they don’t actually need those privileges.

Remove the user from the sysadmin server role whenever possible.

Use the db_datareader and db_datawriter roles to grant users read and write access to any table in the database.

Among other options, you can edit an existing user’s roles using SQL Server Management Studio:

1. First, go to Security, Logins, and right-click a login
2. On the User Mapping tab, uncheck the roles for each database — make sure users still have some form of access needed to run their queries.