June 4, 2018 at 1:01 pm
We use AD groups, SQL Server 2016. One AD group (with over a dozen users) that doesn't have any permissions/privileges to a EmployeeDB, has just 3 users show in the error log where every time they open a New Query in SSMS that their login Login failed for user 'Domain\user3'. Reason: Failed to open the explicitly specified database 'EmployeeDB'. The other users in this AD Group do not have this issue.
I haven't been able to figure out why. Any Ideas?
June 4, 2018 at 1:11 pm
Are they members of more than one AD Group with database access?
June 4, 2018 at 1:12 pm
Sqlraider - Monday, June 4, 2018 1:01 PMWe use AD groups, SQL Server 2016. One AD group (with over a dozen users) that doesn't have any permissions/privileges to a EmployeeDB, has just 3 users show in the error log where every time they open a New Query in SSMS that their login Login failed for user 'Domain\user3'. Reason: Failed to open the explicitly specified database 'EmployeeDB'. The other users in this AD Group do not have this issue.
I haven't been able to figure out why. Any Ideas?
Did you check the default database for the group? Did you also check for membership in other groups?
The error is due to the default database specified for any login or group. And people can be in more than one AD group so you'd want to keep that in mind.
Sue
June 4, 2018 at 1:17 pm
Bert-701015 - Monday, June 4, 2018 1:11 PMAre they members of more than one AD Group with database access?
No.
June 4, 2018 at 1:22 pm
Sue_H - Monday, June 4, 2018 1:12 PMSqlraider - Monday, June 4, 2018 1:01 PMWe use AD groups, SQL Server 2016. One AD group (with over a dozen users) that doesn't have any permissions/privileges to a EmployeeDB, has just 3 users show in the error log where every time they open a New Query in SSMS that their login Login failed for user 'Domain\user3'. Reason: Failed to open the explicitly specified database 'EmployeeDB'. The other users in this AD Group do not have this issue.
I haven't been able to figure out why. Any Ideas?Did you check the default database for the group? Did you also check for membership in other groups?
The error is due to the default database specified for any login or group. And people can be in more than one AD group so you'd want to keep that in mind.Sue
The default database is 'master', but this group isn't in the 'Security/Users' for 'master'. Could that be it? Users are in one and only one AD Group for this Server.
June 4, 2018 at 1:35 pm
Sqlraider - Monday, June 4, 2018 1:22 PMSue_H - Monday, June 4, 2018 1:12 PMSqlraider - Monday, June 4, 2018 1:01 PMWe use AD groups, SQL Server 2016. One AD group (with over a dozen users) that doesn't have any permissions/privileges to a EmployeeDB, has just 3 users show in the error log where every time they open a New Query in SSMS that their login Login failed for user 'Domain\user3'. Reason: Failed to open the explicitly specified database 'EmployeeDB'. The other users in this AD Group do not have this issue.
I haven't been able to figure out why. Any Ideas?Did you check the default database for the group? Did you also check for membership in other groups?
The error is due to the default database specified for any login or group. And people can be in more than one AD group so you'd want to keep that in mind.Sue
The default database is 'master', but this group isn't in the 'Security/Users' for 'master'. Could that be it? Users are in one and only one AD Group for this Server.
Did you disable the guest account for the master database? If so then that is likely the problem.
Sue
June 4, 2018 at 1:44 pm
Run this in master to see what logins have connect permissions to master:
SELECT
USER_NAME(grantee_principal_id) GranteeUserName,
permission_name Permission
FROM sys.database_permissions
WHERE permission_name = 'Connect'
Sue
June 4, 2018 at 1:53 pm
Sue_H - Monday, June 4, 2018 1:35 PMSqlraider - Monday, June 4, 2018 1:22 PMSue_H - Monday, June 4, 2018 1:12 PMSqlraider - Monday, June 4, 2018 1:01 PMWe use AD groups, SQL Server 2016. One AD group (with over a dozen users) that doesn't have any permissions/privileges to a EmployeeDB, has just 3 users show in the error log where every time they open a New Query in SSMS that their login Login failed for user 'Domain\user3'. Reason: Failed to open the explicitly specified database 'EmployeeDB'. The other users in this AD Group do not have this issue.
I haven't been able to figure out why. Any Ideas?Did you check the default database for the group? Did you also check for membership in other groups?
The error is due to the default database specified for any login or group. And people can be in more than one AD group so you'd want to keep that in mind.Sue
The default database is 'master', but this group isn't in the 'Security/Users' for 'master'. Could that be it? Users are in one and only one AD Group for this Server.
Did you disable the guest account for the master database? If so then that is likely the problem.
Sue
No. It says guest is enabled for master. Could the 3 be 'orphaned' but I cannot see it in the GUI?
June 4, 2018 at 1:53 pm
Sue_H - Monday, June 4, 2018 1:44 PMRun this in master to see what logins have connect permissions to master:
SELECT
USER_NAME(grantee_principal_id) GranteeUserName,
permission_name Permission
FROM sys.database_permissions
WHERE permission_name = 'Connect'Sue
Results:
GranteeUserName Permission
dbo CONNECT
guest CONNECT
##MS_PolicyEventProcessingLogin## CONNECT
##MS_AgentSigningCertificate## CONNECT
June 4, 2018 at 2:11 pm
Sqlraider - Monday, June 4, 2018 1:53 PMSue_H - Monday, June 4, 2018 1:44 PMRun this in master to see what logins have connect permissions to master:
SELECT
USER_NAME(grantee_principal_id) GranteeUserName,
permission_name Permission
FROM sys.database_permissions
WHERE permission_name = 'Connect'Sue
Results:
GranteeUserName Permission
dbo CONNECT
guest CONNECT
##MS_PolicyEventProcessingLogin## CONNECT
##MS_AgentSigningCertificate## CONNECT
And I'm leaving off the last of everything I post today. Sorry, thick head today. If nothing else, this one will allow you to check denies as well.
Try: SELECT
USER_NAME(grantee_principal_id) GranteeUserName,
permission_name Permission,
state_desc
FROM sys.database_permissions
WHERE permission_name = 'Connect'
Sue
June 4, 2018 at 3:07 pm
Sue_H - Monday, June 4, 2018 2:10 PMSqlraider - Monday, June 4, 2018 1:53 PMSue_H - Monday, June 4, 2018 1:44 PMRun this in master to see what logins have connect permissions to master:
SELECT
USER_NAME(grantee_principal_id) GranteeUserName,
permission_name Permission
FROM sys.database_permissions
WHERE permission_name = 'Connect'Sue
Results:
GranteeUserName Permission
dbo CONNECT
guest CONNECT
##MS_PolicyEventProcessingLogin## CONNECT
##MS_AgentSigningCertificate## CONNECTAnd I'm leaving off the last of everything I post today. Sorry, thick head today. If nothing else, this one will allow you to check denies as well.
Try:SELECT
USER_NAME(grantee_principal_id) GranteeUserName,
permission_name Permission,
state_desc
FROM sys.database_permissions
WHERE permission_name = 'Connect'Sue
GranteeUserName Permission state_desc
dbo CONNECT GRANT
guest CONNECT GRANT
##MS_PolicyEventProcessingLogin## CONNECT GRANT
##MS_AgentSigningCertificate## CONNECT GRANT
June 4, 2018 at 4:35 pm
Sqlraider - Monday, June 4, 2018 3:07 PMSue_H - Monday, June 4, 2018 2:10 PMSqlraider - Monday, June 4, 2018 1:53 PMSue_H - Monday, June 4, 2018 1:44 PMRun this in master to see what logins have connect permissions to master:
SELECT
USER_NAME(grantee_principal_id) GranteeUserName,
permission_name Permission
FROM sys.database_permissions
WHERE permission_name = 'Connect'Sue
Results:
GranteeUserName Permission
dbo CONNECT
guest CONNECT
##MS_PolicyEventProcessingLogin## CONNECT
##MS_AgentSigningCertificate## CONNECTAnd I'm leaving off the last of everything I post today. Sorry, thick head today. If nothing else, this one will allow you to check denies as well.
Try:SELECT
USER_NAME(grantee_principal_id) GranteeUserName,
permission_name Permission,
state_desc
FROM sys.database_permissions
WHERE permission_name = 'Connect'Sue
GranteeUserName Permission state_desc
dbo CONNECT GRANT
guest CONNECT GRANT
##MS_PolicyEventProcessingLogin## CONNECT GRANT
##MS_AgentSigningCertificate## CONNECT GRANT
Just went back and read your original post...not sure how this went towards master but if the user specifies the database (which they did with that error) but get that failure, they don't have the necessary permissions. Run that same script in the EmployeeDB.
Verify the users are in that AD group with:
EXEC xp_logininfo 'Domain\GroupName', 'members'
And then check one of the users with the errors effective permissions in the EmployeeDB with: EXECUTE AS USER = 'TP420\OneFromGroupWithErrors';
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
REVERT;
Sue
June 5, 2018 at 8:14 am
Sue_H - Monday, June 4, 2018 4:35 PMSqlraider - Monday, June 4, 2018 3:07 PMSue_H - Monday, June 4, 2018 2:10 PMSqlraider - Monday, June 4, 2018 1:53 PMSue_H - Monday, June 4, 2018 1:44 PMRun this in master to see what logins have connect permissions to master:
SELECT
USER_NAME(grantee_principal_id) GranteeUserName,
permission_name Permission
FROM sys.database_permissions
WHERE permission_name = 'Connect'Sue
Results:
GranteeUserName Permission
dbo CONNECT
guest CONNECT
##MS_PolicyEventProcessingLogin## CONNECT
##MS_AgentSigningCertificate## CONNECTAnd I'm leaving off the last of everything I post today. Sorry, thick head today. If nothing else, this one will allow you to check denies as well.
Try:SELECT
USER_NAME(grantee_principal_id) GranteeUserName,
permission_name Permission,
state_desc
FROM sys.database_permissions
WHERE permission_name = 'Connect'Sue
GranteeUserName Permission state_desc
dbo CONNECT GRANT
guest CONNECT GRANT
##MS_PolicyEventProcessingLogin## CONNECT GRANT
##MS_AgentSigningCertificate## CONNECT GRANTJust went back and read your original post...not sure how this went towards master but if the user specifies the database (which they did with that error) but get that failure, they don't have the necessary permissions. Run that same script in the EmployeeDB.
Verify the users are in that AD group with:
EXEC xp_logininfo 'Domain\GroupName', 'members'
And then check one of the users with the errors effective permissions in the EmployeeDB with:EXECUTE AS USER = 'TP420\OneFromGroupWithErrors';SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
REVERT;Sue
When I run this:EXEC xp_logininfo 'Domain\GroupName', 'members'
I get this error: Could not obtain information about Windows NT group/user 'Domain\GroupName'
But when I run it for any other AD Groups it returns all the members.
When I check with fn_my_permissions with one of the users in question they do not have permissions to EmployeeDB which is as it should be.
June 5, 2018 at 10:44 am
Sqlraider - Tuesday, June 5, 2018 8:14 AMSue_H - Monday, June 4, 2018 4:35 PMJust went back and read your original post...not sure how this went towards master but if the user specifies the database (which they did with that error) but get that failure, they don't have the necessary permissions. Run that same script in the EmployeeDB.
Verify the users are in that AD group with:
EXEC xp_logininfo 'Domain\GroupName', 'members'
And then check one of the users with the errors effective permissions in the EmployeeDB with:EXECUTE AS USER = 'TP420\OneFromGroupWithErrors';SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
REVERT;Sue
When I run this:
EXEC xp_logininfo 'Domain\GroupName', 'members'
I get this error: Could not obtain information about Windows NT group/user 'Domain\GroupName'But when I run it for any other AD Groups it returns all the members.
When I check with fn_my_permissions with one of the users in question they do not have permissions to EmployeeDB which is as it should be.
Some groups can't be enumerated with xp_logininfo - forests with one-way trusts have issues, not enough permissions, not sure what else. You can try to use Powershell to get the group members. Follow the article up here on doing so:
Powershell To Get Active Directory Users And Groups into SQL
Did you also check fn_my_permissions with a user who does not get the error?
Effective permissions take into account cumulative permissions which is how it works in SQL Server. The effective permissions are based on all of the permissions the user has through their own login, permissions from AD groups they are members of and permissions from roles they are members of. Deny takes precedence so if a user is a member of a group with grant and deny, the deny would take effect.
If some users in that group have permissions and some don't, they aren't members of any other groups and only get their permissions from that one group, something is missing from that scenario. As a test, I would try to grant one of those users an individual login and set it up how the group is setup so they have access to the EmployeeDB with same permissions. If they still get denied, then there is something explicitly denying their access. If they can connect, then maybe it's something with that group that is causing the problem. Or maybe they aren't members of that group.
Sue
June 5, 2018 at 12:24 pm
Sue_H - Tuesday, June 5, 2018 10:44 AMSqlraider - Tuesday, June 5, 2018 8:14 AMSue_H - Monday, June 4, 2018 4:35 PMJust went back and read your original post...not sure how this went towards master but if the user specifies the database (which they did with that error) but get that failure, they don't have the necessary permissions. Run that same script in the EmployeeDB.
Verify the users are in that AD group with:
EXEC xp_logininfo 'Domain\GroupName', 'members'
And then check one of the users with the errors effective permissions in the EmployeeDB with:EXECUTE AS USER = 'TP420\OneFromGroupWithErrors';SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
REVERT;Sue
When I run this:
EXEC xp_logininfo 'Domain\GroupName', 'members'
I get this error: Could not obtain information about Windows NT group/user 'Domain\GroupName'But when I run it for any other AD Groups it returns all the members.
When I check with fn_my_permissions with one of the users in question they do not have permissions to EmployeeDB which is as it should be.Some groups can't be enumerated with xp_logininfo - forests with one-way trusts have issues, not enough permissions, not sure what else. You can try to use Powershell to get the group members. Follow the article up here on doing so:
Powershell To Get Active Directory Users And Groups into SQLDid you also check fn_my_permissions with a user who does not get the error?
Effective permissions take into account cumulative permissions which is how it works in SQL Server. The effective permissions are based on all of the permissions the user has through their own login, permissions from AD groups they are members of and permissions from roles they are members of. Deny takes precedence so if a user is a member of a group with grant and deny, the deny would take effect.If some users in that group have permissions and some don't, they aren't members of any other groups and only get their permissions from that one group, something is missing from that scenario. As a test, I would try to grant one of those users an individual login and set it up how the group is setup so they have access to the EmployeeDB with same permissions. If they still get denied, then there is something explicitly denying their access. If they can connect, then maybe it's something with that group that is causing the problem. Or maybe they aren't members of that group.
Sue
yep, I ran fn_my_permissions on both those getting the error and those not getting the error. Everything looks as it should permissions wise for this AD group. I just can't figure out why some users when opening up a New Query window in SSMS it tries to access a database they do not have permissions to.
I'm grasping at straws here, could it be they are using an ODBC connection that they selected this database to connect to?
Thanks,
Sqlraider
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply