Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Marlon Ribunal - SQL, Code, Coffee, etc.

I'm passionate about SQL Server. But I feel like I haven't reached my full potential yet. So, this is my mission: My purpose is to help people in their pursuit of growth and development; and, thereby, help myself realize my full potential as a professional, husband, father, christian, and human being.
My online presences include: Tech Blog: Marlon Ribunal - SQL, Code, Coffee, etc. Productivity & GTD Hack Blog: Productivity Bits Twitter: @MarlonRibunal

Limiting user access to your database

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.

Comments

Leave a comment on the original post [marlonribunal.com, opens in a new window]

Loading comments...