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