Why was the select permission denied?

Question

I am having trouble granting permissions to users of my database. For instance, I cannot seem to get my user SELECT privileges no matter how many securables and memberships I give it. I started by giving the user select permission database>security>Users>Properties>securables and giving it db_datareader membership. After this did not work I added the user to all of the memberships and granted him all permissions available in the securables section. After that failed, I gave the user all permissions available in the security>login>properties, I added the login to all server roles accept sysadmin and gave the user ownership of all schemas in the database I want him to access. Still I get this same error below:

 The SELECT permission was denied on object 'Patient_Information', database 'Clinical_Data', schema 'dbo'

When I add the login to the role sysadmin, the user that it is mapped to has no problem doing selects, inserts and basically anything else. The weird thing is that when I look into database>properties>Permissions the user does not have any of the permissions that I have granted him in the securables section. Here is the code I use to grant:

 USE Clinical_Data; GRANT Select on schema::DBO to lab31

Thanks in advance for any help you can provide.

Answer 1

I usually create a database role and assign the user to the role. Assign the schema privileges to the database roles. A quick example of this using code for a fictitious database is below.

-- 
-- 1 - GRANTING CORRECT USER ACCESS BY SCHEMA
-- 

--create test user login
CREATE LOGIN [User1] WITH PASSWORD=N'p@55w0rd'
GO

-- Make sure we are in autos
USE [AUTOS]
GO

--create user in test database
CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[ACTIVE]
GO

--create role
CREATE ROLE [Auto_User] AUTHORIZATION [dbo]
GO

--apply permissions to schemas
GRANT ALTER ON SCHEMA::[ACTIVE] TO [Auto_User]
GO

GRANT CONTROL ON SCHEMA::[ACTIVE] TO [Auto_User]
GO

GRANT SELECT ON SCHEMA::[ACTIVE] TO [Auto_User]
GO

--ensure role membership is correct
EXEC sp_addrolemember N'Auto_User', N'User1'
GO

If you are more comfortable with SQL Server Management Studio, here is a go. All actions might not be exact but I did check them with SS 2012.

Lets say your login is [bilbo] and the database they want to access is [middle earth].

1. Is the default database for the server login = [bilbo] the database they are trying to query, [middle earth]?

   [SMSS object explorer path]: Server -> security -> logins -> right click + properties

2. Is the server login mapped to a database user named [bilbo] in the [middle earth] database?

   [SMSS object explorer path]: Database name -> security -> users

3. Make sure the user [bilbo] is not in any deny database roles (db_denydatareader, db_denydatawriter). Any deny actions over ride any grants.

   [SMSS object explorer path]: Database name -> security -> roles -> database roles -> select + right click + properties

   {You would add your custom database role here.}

4. Make sure the user [bilbo] has permissions to the schema.

   [SMSS object explorer path]: Database name -> security -> schemas -> select + right click + properties

This should give you the layout of the land.

Find the offending revoke or lack of permission and assign it.

Please do not give out all server roles or all database roles! You are just asking for a head ache when the user does something stupid like drop table.