August 23, 2017 at 5:16 am
Good morning Experts,
An user is getting the below error:
The SELECT permission was denied on the object 'abc', database 'xyz', schema 'dbo'
I checked if he has any DENY permissions, but he does not have any. The user is mapped correctly to login with db_datareader role.Could you please advise.
August 23, 2017 at 5:25 am
If the login was mapped to a user with the db_reader role, you wouldn't be seeing that error.
I assume that the query is not cross database? Can the user select from any other objects on the database?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 23, 2017 at 5:33 am
Does the user have the db_datareader database role in the correct database?
If so, has this database been restored from a different server? If so, the SIDs of the login and user might be mismatched. You can run this procedure in your database [abc] to get a list of them:
EXECUTE sp_change_users_login @Action = 'report';
August 23, 2017 at 5:57 am
Ed Wagner - Wednesday, August 23, 2017 5:33 AMDoes the user have the db_datareader database role in the correct database?If so, has this database been restored from a different server? If so, the SIDs of the login and user might be mismatched. You can run this procedure in your database [abc] to get a list of them:
EXECUTE sp_change_users_login @Action = 'report';
The user is windows user . So same SID. Also, user is not doing cross database query.
August 23, 2017 at 5:59 am
coolchaitu - Wednesday, August 23, 2017 5:57 AMThe user is windows user . So same SID. Also, user is not doing cross database query.
Then the permissions are wrong. As I asked before, can the user SELECT from any other objects on the database?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 23, 2017 at 7:30 am
If the permissions were granted in the correct database, roles or group membership could play a part as well since permissions are cumulative with deny taking precedence. Check the permissions for the object 'abc', database 'xyz', schema 'dbo' itself. Check to see if any Windows groups or roles have deny permissions. You'd also want to check if the user is in the db_denydatareader role.
Sue
August 23, 2017 at 7:36 am
coolchaitu - Wednesday, August 23, 2017 5:16 AMGood morning Experts,
An user is getting the below error:
The SELECT permission was denied on the object 'abc', database 'xyz', schema 'dbo'
I checked if he has any DENY permissions, but he does not have any. The user is mapped correctly to login with db_datareader role.Could you please advise.
Just to be sure about the DENY permissions, can you check the results of the query below (after changing the filter to the correct username)?
Keep in mind that DENY permissions can be set on the object level, but it is also possible to get it from a database role (like "db_denydatareader" or a self defined role).
select db_perm.*, db_prin.name as grantee
from sys.database_permissions db_perm
inner join sys.database_principals db_prin
on db_perm.grantee_principal_id = db_prin.principal_id
where db_prin.name = 'your_user'
August 23, 2017 at 8:12 am
I have modified my previous query a bit. The query below displays ALL deny permissions in the database and shows to which account or role it is granted. The last OR clause can be used to include any (other) specific permissions on the object from the error message.
select object_name(major_id) as object, db_perm.permission_name, db_perm.state_desc, db_prin.name, db_prin.type_desc
from sys.database_permissions db_perm
inner join sys.database_principals db_prin
on db_perm.grantee_principal_id = db_prin.principal_id
where state = 'D'
or db_perm.major_id = OBJECT_ID('abc')
August 23, 2017 at 8:42 am
HanShi - Wednesday, August 23, 2017 8:12 AMI have modified my previous query a bit. The query below displays ALL deny permissions in the database and shows to which account or role it is granted. The last OR clause can be used to include any (other) specific permissions on the object from the error message.
select object_name(major_id) as object, db_perm.permission_name, db_perm.state_desc, db_prin.name, db_prin.type_desc
from sys.database_permissions db_perm
inner join sys.database_principals db_prin
on db_perm.grantee_principal_id = db_prin.principal_id
where state = 'D'
or db_perm.major_id = OBJECT_ID('abc')
I have used your script and did not find any deny permission for the object.
August 23, 2017 at 10:03 am
I have a few thoughts on this.
You could have a stored procedure that was created with an "EXECUTE AS" in it that might cause it to run as a different user who may not have access to the table.
Are you certain that the user is connected to the database you are looking at? I know I've seen times where an application gets released with a typo in it so the program is writing data to our TEST servers instead of live. I'd run sp_who2 to ensure that they are connected to the SQL instance you expect them to be connected to.
If they ARE connecting to the proper instance and you are sure they are not running something as a different user (applications can be written to do impersonation as well, so it could be the application is connecting as a different user that doesn't have permissions), try running:
USE [xyz]
EXECUTE AS LOGIN = <username>
GO
SELECT USER_NAME() -- This is to verify that you are running the query as the expected login
SELECT TOP 5 *
FROM xyz.dbo.abc
GO
REVERT
GO
SELECT USER_NAME() -- This is to verify that you are running the query as you again
Doing that, presuming you have impersonate permissions, will let you know if the user has access to the table. If you don't have impersonate permissions, talk to your DBA to run that as someone with sysadmin permissions.
TL;DR version - is the request to pull data from that table being done using some form of impersonation (SQL side or application side)?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 23, 2017 at 11:01 am
bmg002 - Wednesday, August 23, 2017 10:03 AMI have a few thoughts on this.
You could have a stored procedure that was created with an "EXECUTE AS" in it that might cause it to run as a different user who may not have access to the table.
Are you certain that the user is connected to the database you are looking at? I know I've seen times where an application gets released with a typo in it so the program is writing data to our TEST servers instead of live. I'd run sp_who2 to ensure that they are connected to the SQL instance you expect them to be connected to.If they ARE connecting to the proper instance and you are sure they are not running something as a different user (applications can be written to do impersonation as well, so it could be the application is connecting as a different user that doesn't have permissions), try running:
USE [xyz]
EXECUTE AS LOGIN = <username>
GO
SELECT USER_NAME() -- This is to verify that you are running the query as the expected login
SELECT TOP 5 *
FROM xyz.dbo.abc
GO
REVERT
GO
SELECT USER_NAME() -- This is to verify that you are running the query as you againDoing that, presuming you have impersonate permissions, will let you know if the user has access to the table. If you don't have impersonate permissions, talk to your DBA to run that as someone with sysadmin permissions.
TL;DR version - is the request to pull data from that table being done using some form of impersonation (SQL side or application side)?
Thanks a lot for the script . The user is a member of several AD groups. I think one of the AD group has deny permission. Any script to find out which AD group is the culprit
August 23, 2017 at 11:11 am
coolchaitu - Wednesday, August 23, 2017 11:01 AMThanks a lot for the script . The user is a member of several AD groups. I think one of the AD group has deny permission. Any script to find out which AD group is the culprit
That's kind of what I thought when I posted. Check the permissions on the object itself and check the deny data reader role. You may find what group has the deny and then can work from there.
Sue
August 23, 2017 at 11:27 am
Sue_H - Wednesday, August 23, 2017 11:11 AMcoolchaitu - Wednesday, August 23, 2017 11:01 AMThanks a lot for the script . The user is a member of several AD groups. I think one of the AD group has deny permission. Any script to find out which AD group is the culprit
That's kind of what I thought when I posted. Check the permissions on the object itself and check the deny data reader role. You may find what group has the deny and then can work from there.
Sue
Hi Sue,
Could you please send me a script for this
August 23, 2017 at 12:11 pm
coolchaitu - Wednesday, August 23, 2017 11:27 AMSue_H - Wednesday, August 23, 2017 11:11 AMcoolchaitu - Wednesday, August 23, 2017 11:01 AMThanks a lot for the script . The user is a member of several AD groups. I think one of the AD group has deny permission. Any script to find out which AD group is the culprit
That's kind of what I thought when I posted. Check the permissions on the object itself and check the deny data reader role. You may find what group has the deny and then can work from there.
Sue
Hi Sue,
Could you please send me a script for this
If you look up the documentation for the database permission views, they have sample scripts. Usually you just need to add whatever user, role, object you want to filter by. You can also search the scripts up on this site.
These are from the documentation:
--The following query returns the members of the database roles.
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
--added the role db_denydatareader
AND DP1.name = 'db_denydatareader'
ORDER BY DP1.name;
--Listing permissions on schema objects within a database
SELECT pr.principal_id, pr.name, pr.type_desc,
pe.state_desc,
pe.permission_name, s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.objects AS o
ON pe.major_id = o.object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
--Add the object name here
WHERE o.name = 'YourObjectName';
Sue
August 23, 2017 at 12:28 pm
here's an attempt to list all security associated to a specific login, it's AD groups, and any database roles they belong to:/* all permissions */
--check for any AD groups
CREATE TABLE #ADinfo (
AccountName nvarchar(128),
AccountType char(8), --user or group
Privilege char(9), --admin, user, or null.
MappedLogin nvarchar(128), --the mapped login name by using the mapped rules
PermissionPath nvarchar(128));
INSERT INTO #ADinfo EXEC xp_logininfo N'DomainName\UserName','all';
--lookup database roles and any permissions
WITH member(principal_id, name) AS
(SELECT u.principal_id, u.name
FROM #ADinfo a
INNER JOIN sys.database_principals u ON ISNULL(a.PermissionPath, a.MappedLogin) = u.name
UNION ALL
SELECT rm.role_principal_id, r.name
FROM sys.database_role_members rm
INNER JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
INNER JOIN member m ON rm.member_principal_id = m.principal_id)
SELECT m.name AS user_role, p.state_desc, p.permission_name, p.class_desc,
CASE WHEN p.class_desc = 'OBJECT_OR_COLUMN' THEN SCHEMA_NAME(o.schema_id) ELSE s.name END AS schema_name, o.name AS object_name
FROM (SELECT DISTINCT principal_id, name FROM member) m
LEFT OUTER JOIN sys.database_permissions p ON m.principal_id = p.grantee_principal_id
LEFT OUTER JOIN sys.objects o ON p.major_id = o.object_id AND p.class_desc = 'OBJECT_OR_COLUMN'
LEFT OUTER JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class_desc = 'SCHEMA'
ORDER BY s.name, o.name, m.name;
DROP TABLE #ADinfo;
just change the DomainName\Username in the call to xp_logininfo
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply