The SELECT permission was denied on the object 'abc', database 'xyz', schema 'dbo'

Question

The SELECT permission was denied on the object 'abc', database 'xyz', schema 'dbo'

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

coolchaitu SSChampion Points: 10332 More actions · Answer 1

Chris Harshman - Wednesday, August 23, 2017 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

Thanks a lot for the script. Chris i want to know in which all AD groups the user is member of. Could you please let me know how to get this information

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

this command is the part that determines all the AD groups an AD user has database access through:
EXEC xp_logininfo N'DomainName\UserName','all';

coolchaitu SSChampion Points: 10332 More actions · Answer 3

Chris Harshman - Wednesday, August 23, 2017 7:10 PM
this command is the part that determines all the AD groups an AD user has database access through:
EXEC xp_logininfo N'DomainName\UserName','all';

Hi Chris,

Here is the actual scenario: There is a windows user NTLAB\E15770 who is added to several AD groups. These AD Groups are created as logins in the server. Eg. [NTLAB\db-AFLAC_56_DEV-PROG-D], [NTLAB\db-AFLAC_56_DEV-DBO-D] and [NTLAB\db-AFLAC_56_DEV-R-D(These are WIndows Groups created as logins in the server. So, I want to know to which all AD Group logins the user belongs to.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

EXEC xp_logininfo N'NTLAB\E15770','all';
this should do that. the user specified doesn't need to be directly created as a login or user in SQL Server, just have some access through an AD group
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-logininfo-transact-sql

coolchaitu SSChampion Points: 10332 More actions · Answer 5

Chris Harshman - Wednesday, August 23, 2017 8:00 PM
EXEC xp_logininfo N'NTLAB\E15770','all';
this should do that. the user specified doesn't need to be directly created as a login or user in SQL Server, just have some access through an AD group
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-logininfo-transact-sql

Hi Chris,
It is not returning any result. Could you please advise

HanShi SSC-Dedicated Points: 33506 More actions · Answer 6

coolchaitu - Wednesday, August 23, 2017 7:55 PM
Chris Harshman - Wednesday, August 23, 2017 7:10 PM
this command is the part that determines all the AD groups an AD user has database access through:
EXEC xp_logininfo N'DomainName\UserName','all';
Hi Chris,
Here is the actual scenario: There is a windows user NTLAB\E15770 who is added to several AD groups. These AD Groups are created as logins in the server. Eg. [NTLAB\db-AFLAC_56_DEV-PROG-D], [NTLAB\db-AFLAC_56_DEV-DBO-D] and [NTLAB\db-AFLAC_56_DEV-R-D(These are WIndows Groups created as logins in the server. So, I want to know to which all AD Group logins the user belongs to.

It could be the "xp_logininfo" procedure doesn't return results because the account is not directly added to the groups that are a login in SQL. It could be the Windows AD groups that are added as login in SQL contain nested AD groups and these nested AD groups contain the actual users. This nesting can go several levels deep, depending on the design of your AD. Best option is to talk to your AD admin to view/list the (complete and nested) membership. I know there are Powershell scripts available on the web to list such nested membership, but I don't have actual links at hand.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Thom A SSC Guru Points: 99126 More actions · Answer 7

HanShi - Thursday, August 24, 2017 4:49 AM
It could be the "xp_logininfo" procedure doesn't return results because the account is not directly added to the groups that are a login in SQL. It could be the Windows AD groups that are added as login in SQL contain nested AD groups and these nested AD groups contain the actual users. This nesting can go several levels deep, depending on the design of your AD. Best option is to talk to your AD admin to view/list the (complete and nested) membership. I know there are Powershell scripts available on the web to list such nested membership, but I don't have actual links at hand.

Powershell would be:
Get-ADPrincipalGroupMembership Thom | Select name
You may need to enable, and import some extra modules.
Enable to module (you'll need to run this as local administrator)
Add-WindowsFeature RSAT-AD-Powershell
Import Activedirectory (do this in the same session as the first code:
Import-Module ActiveDirectory

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Ed Wagner SSC Guru Points: 287024 More actions · Answer 8

Another option is to go to a DOS prompt and use this:

net user UserName /domain

If that doesn't work for you, your DBA or network administrator should be able to help you find the list of groups.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

coolchaitu SSChampion Points: 10332 More actions · Answer 9

Ed Wagner - Thursday, August 24, 2017 5:18 AM
Another option is to go to a DOS prompt and use this:
net user UserName /domain
If that doesn't work for you, your DBA or network administrator should be able to help you find the list of groups.

This worked Ed. Thanks a lot

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 10

Did you run that statement in the database that you are checking permissions in? Sorry, I guess I could have explained that better.

coolchaitu SSChampion Points: 10332 More actions · Answer 11

Chris Harshman - Thursday, August 24, 2017 7:06 AM
Did you run that statement in the database that you are checking permissions in? Sorry, I guess I could have explained that better.

I ran in command prompt. You explained very very well Chris. Thanks a lot.

Arsh SSCertifiable Points: 6345 More actions · Answer 12

How about this user's access to other objects? Is this a new user, how was the behavior before?