Last 22th September I had the great pleasure to participate as a speaker in SQL Saturday #722 in Lisbon, where I presented a few demos regarding Permissions Management!
Since then, several attendees contacted me asking if I could share the demos I made in my session. I told them I’d put them in my blog, so here it goes, this will be the first of them, Demo #1 🙂
Before the demos however, I’d like to start by giving some context for this series of posts, along with the motivation for the presentation itself. While working as a DBA, I’ve found (as many of you, I’m sure) a great number of situations where:
- Users are database owners, simply because they have to be able to truncate tables and read/write data.
- Users became sysadmin because they needed to:
– Bulk Insert into one table in one database
– Execute xp_cmdshell
– View exec runtime stats and execution plans
– Execute cross database queries
– Clear the Buffer Cache
Why is this a problem?
This couldn’t be further from the principle of least privilege, a sysadmin can perform any task in any database in the entire SQL Server instance, as well as manipulate features at the instance level that can even go beyond the scope of SQL Server.
This approach highly contributes to:
– Compromise system stability
– Increase vulnerability to inner and outer attacks
– Generate problems in deployment
– Unnecessary data exposure!
A better approach…
– Any need of access by users or resources should be previously known, properly justified and stipulated as a requisite.
– All accounts and respective privileges should be formally documented and appropriately approved.
– Users or resources should then be provided with the minimum privileges necessary to fulfill their roles and responsibilities according to the specified requisites.
Materializing privileges in SQL Server
Privileges will basically map to Permissions in SQL Server and when we speak about permissions, it is mandatory to also speak about Principals and Securables.
Simply put, Principals typically represent logins, users and roles that will have determined Permissions to access the Securables (ex: logins, DB Server, databases, schemas, tables, stored procedures, etc.)
This first demo covers how we can check the set of permissions and role membership for any login at the instance level, using T-SQL.
Yes, it is possible to do it using the GUI in SSMS (or other graphical tools) but if you have many databases and many users to check, it can become a (very) hard and time consuming task whereas using a T-SQL script it remains simple and fast, besides it allows to collect evidence very easily and effectively and also automation of maintenance tasks, so I think it’s definitely a very cool thing!
Where to find full list of permissions
Or using T-SQL:
SELECT * FROM sys.fn_builtin_permissions(null)
we get this result, with 238 built in permissions in SQL 2017:
As we can see, given its high granularity, it becomes easy to grant no more than the necessary permissions for each principal.
Effective vs Explicit permissions
Bear in mind that the Effective Permissions that a Principal has on a Securable come from the ones that are:
– Explicitly Granted (Explicit)
– Inherited through role membership
– Defined on a securable at a higher level (ex: Schema)
Note: Permissions can be granted both to SQL Server logins and Database Users.
– Using permissions we can GRANT or DENY access from principals to securables.
– GRANT is used to assign a permission.
– DENY is used to deny a permission that was given either directly or inherited through role membership.
– REVOKE removes (undo) the last grant or deny.
Bear in mind that DENY always takes precedence over GRANT with the following exception:
– a column-level GRANT takes precedence over a table-level DENY.
but even this exception can disappear if the configuration option “common criteria compliance enabled” gets enabled at instance level (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/common-criteria-compliance-enabled-server-configuration-option)
DEMO #1 (T-SQL based)
Let’s start by creating a new login:
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'AfonsoHenriques')
DROP LOGIN AfonsoHenriques
CREATE LOGIN AfonsoHenriques WITH PASSWORD = 'S0meStr@ngP#$$Word' MUST_CHANGE, CHECK_POLICY = ON, CHECK_EXPIRATION = ON
Checking Server Roles membership for him:
SELECT r.name AS RoleName,
p.name AS PrincipalName
FROM sys.server_role_members AS srm
INNER JOIN sys.server_principals AS r
ON srm.role_principal_id = r.principal_id
INNER JOIN sys.server_principals AS p
ON srm.member_principal_id = p.principal_id
WHERE p.name = 'AfonsoHenriques'
Makes sense, since I didn’t add him to any Server Role yet, he only belongs to public.
Adding AfonsoHenriques to the sysadmin server role:
ALTER SERVER ROLE sysadmin ADD MEMBER AfonsoHenriques
If we run the above query to check the server roles membership again, now we can see that AfonsoHenriques is almighty:
What about his Explicit Permissions?
SELECT p.name, p.type_desc, sp.permission_name, sp.state_desc
FROM sys.server_permissions sp
INNER JOIN sys.server_principals p
ON sp.grantee_principal_id = p.principal_id
where p.name = 'AfonsoHenriques'
Again, it makes sense, CONNECT SQL is added by default and no more permissions were explicitly granted.
Ok, does this mean that the man is a sysadmin and his only permission is to be able to connect to SQL Server?! Nope, let’s now check the effective permissions, i.e. besides the ones explicitly granted, also the ones he inherited by role membership when he became a sysadmin. This can be done by using a system function called: fn_my_permissions
If you are a DBA and need to check someone else’s effective permissions, at the login level, you can easily do it, using login impersonation. By changing the execution context with “Execute as login=’AfonsoHenriques'”, fn_my_permissions will actually show AfonsoHenriques permissions. REVERT statement will get me back to myself 😉
EXECUTE AS LOGIN = 'AfonsoHenriques'
SELECT SUSER_SNAME() as LoginName, permission_name FROM fn_my_permissions(NULL, 'SERVER') --DATABASE
He is almighty all right!
Stay tuned for the next demo, coming here soon, where I show a typical case where logins get to be sysadmin without any need for it!!