BULK INSERT without needing to be a privileged user

Hi,

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 is the third and last, Demo #3 ūüôā

Continue reading “BULK INSERT without needing to be a privileged user”

Advertisements

How to Check Logins Membership and Server level Permissions

Hi,

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.)

MaterializePrivileges

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

https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-2017

Or using T-SQL:

SELECT * FROM sys.fn_builtin_permissions(null)

we get this result, with 238 built in permissions in SQL 2017:

CheckingLoginAndUserPermissions

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

– 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
GO

CREATE LOGIN AfonsoHenriques WITH PASSWORD = 'S0meStr@ngP#$$Word' MUST_CHANGE, CHECK_POLICY = ON, CHECK_EXPIRATION = ON
GO

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'

NoServerRoles

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:

ServerRoles.png

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'

explicitPermissions

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 ūüėČ

USE master
EXECUTE AS LOGIN = 'AfonsoHenriques'
SELECT SUSER_SNAME() as LoginName, permission_name FROM fn_my_permissions(NULL, 'SERVER') --DATABASE
REVERT
GO
SELECT SUSER_SNAME()

EffectivePermissions

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!!

Cheers ūüėČ

What is Server Side Trace and how to use it

Hi!

This is the second of three articles dedicated to demonstrations  I made about auditing on SQL Saturday #685 and 24 HOP Portuguese Edition last month. You can find the first post, concerning default trace here.

If for any reason¬†(legal, business rules, etc.) you¬†need to know who’s¬†doing what in your SQL Server Instance or Database(s), keep on reading…

Continue reading “What is Server Side Trace and how to use it”

What is Default trace and how to use it

Hi!

On the 28th October I had the pleasure and privilege to¬†go to Porto speaking a bit about Auditing in SQL Server in the¬†SQL Saturday #685 event. Last week I¬†was very pleased to do¬†the same presentation in 24 HOP Portuguese Edition. Meanwhile, many people asked me¬†about the¬†demos,¬†so as promised I’ll put them here ūüôā

This will be the first of three posts¬†regarding those presentations and today I’ll introduce: Default Trace.

Continue reading “What is Default trace and how to use it”

Mantaining Query Store at the instance level in SQL Server 2016

Hi,

Query Store is a great new feature that comes with SQL Server 2016 and I suspect will have a large acceptance among the DBAs.

As DBAs we need to manage not just one but many/all databases in one (or many) instance(s).

Continue reading “Mantaining Query Store at the instance level in SQL Server 2016”

Query Store New in SQL Server 2016 ‚Äď WITH RECOMPILE?

Hi,

This is part VIII of several posts about a new feature in SQL Server 2016 called: Query Store!
You can find Part VII here where I showed that using Query Store it’s possible to get the execution plans even for encrypted stored procedures.

If you create a Stored Procedure with the RECOMPILE option, will you have access to its runtime statistics and execution plan?
In today’s post I’ll answer this question comparing DMOs and Query Store ūüôā
Continue reading “Query Store New in SQL Server 2016 ‚Äď WITH RECOMPILE?”