Limiting user access to your database

MarlonRibunal, 2012-04-11

There are situations where you need to grant SELECT permission to a particular user and limit that access to a particular OBJECT, say a TABLE.

Here’s a quick way of accomplishing this. In this example we’re using the AdventureWorks2008R2 database.

I have already created the LOGIN in advance (“TestUser01“). Let’s map that to a USER Object:

CREATE USER [TestUser01] FOR LOGIN [TestUser01]
GO

The next step is to set the permission on the particular table we want the user to access:

Grant SELECT To User For  A Particular Table

You can further restrict access at the Column Level by setting the Column Permission as needed. Using the HAS_PERMS_BY_NAME  function in SQL Server, let’s check if we’ve granted user TestUser01 the SELECT permission he needed for the Employee table:

SELECT HAS_PERMS_BY_NAME
 ('AdventureWorks2008R2.HumanResources.Employee', 'OBJECT', 'SELECT')
 AS SELECT_PERM,
 name
 AS TABLE_NAME,
 type_desc, schema_id
FROM sys.tables

The HAS_PERMS_BY_NAME function returns true (1) or false (0) to indicate whether permission has been granted or not.

HAS_PERMS_BY_NAME Function returning TRUE

The good thing about HAS_PERMS_BY_NAME is that, it is accessible to the Public role – meaning Users with minimal access level can run a query containing the function. Or if you are an Admin and you want to test a newly altered user you can impersonate that user:

EXECUTE AS USER = 'TestUser01'
GO
SELECT HAS_PERMS_BY_NAME
('AdventureWorks2008R2.HumanResources.Employee', 'OBJECT', 'SELECT')
AS SELECT_PERM,
name AS TABLE_NAME,
type_desc, schema_id
FROM sys.tables
GO
REVERT
GO

That should give us the same result shown above. This is how it looks like when TestUser01 logs in to the AdventureWorks2008R2 database:

Limiting User Access to a SQL Server 2008 R2 database

No related posts.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads