Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Easily Testing User Permissions
Published: Nov 26, 2020
This is a quick post just to highlight a simple concept that many people don’t know exists.

Every DBA should be implementing tough security rules and permissions across their estate, but this can be very hard to do without having the ability to test any security amendments you may be making.

Most people I have spoken to will tend to create a SQL User, allocate / amend permissions, and then they open a new instance of SQL Server Management Studio, logging in as the SQL User, and then test the permissions accordingly.

This is fine if you’re checking high level permissions (such as database access), but if you’re simply wanting to check that someone can read from a table or maybe execute a stored procedure, then there are easier ways.

Let’s make a simple scenario where we have created 2 new SQL Users and wish to validate that one is able to read from a table and the other isn’t:

use AdventureWorks2012
go

create user myReadUser without login
create user myNoReadUser without login
go

grant select on sales.salesOrderHeader to myReadUser
deny select on sales.salesOrderHeader to myNoReadUser
go


Now, we can use “execute as” in order to run tSQL under another user’s context. The only thing to remember is to use “revert” in order to put the context back into your own access:

-- test as myReadUser
execute as user = 'myReadUser'

select top 10 *
from sales.SalesOrderHeader

revert
go



Now as the no read user:

-- test as myNoReadUser
execute as user = 'myNoReadUser'

select top 10 *
from sales.SalesOrderHeader

revert
go



And there you can see how you can easily test user access without having to switch SSMS windows or completely log in as another user.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.