Still using T-SQL cursors?….

Last week while I was delivering a T-SQL course (beginners level), I mentioned the existence of cursors, just to highlight that they should not be used and that nowadays there are better alternatives for pretty much any situation where a cursor would be used.

Why are cursors bad?

Continue reading “Still using T-SQL cursors?….”


BULK INSERT without needing to be a privileged user


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”

How to Check Logins Membership and Server level Permissions


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

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


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


Checking Server Roles membership for him:

SELECT AS RoleName, 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 = '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.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 = '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 😉

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

Cheers 😉

What is Server Side Trace and how to use it


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


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


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”