2017 group permissions

Question

2017 group permissions

smoham22

Mr or Mrs. 500

Points: 525
More actions
June 4, 2020 at 3:43 pm

#3760550

I am having weird issue with permissions. I granted a group read write execute to database but the user is still not able to connect to database with the following error "Ther Server principal "User" is not able to access database "Userdatabase"under the current security context. But when I add the user individually the user is able to access the database.
This user is part of another group which does not have access to the database the user is trying to access. could it be its getting limited by that.
Thanks in advance.

Viewing 2 posts - 1 through 1 (of 1 total)

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

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

The permissions for users are cumulative, so as long as they are in at least 1 group that has access to the database then they should have access. So this new group that you granted these permissions to has a login on the instance, and an associated user in the "Userdatabase" with the permissions applied to that user?

You can run this to verify if the user does have access through a login:

EXEC xp_logininfo N'domainname\useraccount','all';

Then within the database "UserDatabase" you can check their permissions:

DECLARE @user_role sysname = N'domainname\useraccount';

--check for any AD groups
DECLARE @ADInfo TABLE (
    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 @user_role,'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 p.class_desc
        WHEN 'OBJECT_OR_COLUMN' THEN CONCAT(QUOTENAME(SCHEMA_NAME(o.schema_id)), '.', QUOTENAME(o.name))
        WHEN 'SCHEMA' THEN QUOTENAME(s.name)
        WHEN 'DATABASE_PRINCIPAL' THEN QUOTENAME(i.name)
        WHEN 'TYPE' THEN QUOTENAME(t.name)
        ELSE o.name
    END AS object_name,
    CASE p.class_desc WHEN 'OBJECT_OR_COLUMN' THEN
        CONCAT(p.state_desc, ' ', CAST(p.permission_name AS varchar(80)) COLLATE SQL_Latin1_General_CP1_CI_AS, ' ON ', QUOTENAME(SCHEMA_NAME(o.schema_id)), '.', QUOTENAME(o.name), ' TO ', QUOTENAME(m.name), ' AS ', QUOTENAME(g.name), ';')
    WHEN 'SCHEMA' THEN
        CONCAT(p.state_desc, ' ', CAST(p.permission_name AS varchar(80)) COLLATE SQL_Latin1_General_CP1_CI_AS, ' ON SCHEMA::', QUOTENAME(s.name), ' TO ', QUOTENAME(m.name), ' AS ', QUOTENAME(g.name), ';')
    WHEN 'DATABASE_PRINCIPAL' THEN
        CONCAT(p.state_desc, ' ', CAST(p.permission_name AS varchar(80)) COLLATE SQL_Latin1_General_CP1_CI_AS, ' ON USER::', QUOTENAME(i.name), ' TO ', QUOTENAME(m.name), ' AS ', QUOTENAME(g.name), ';')
    WHEN 'TYPE' THEN
        CONCAT(p.state_desc, ' ', CAST(p.permission_name AS varchar(80)) COLLATE SQL_Latin1_General_CP1_CI_AS, ' ON TYPE::', QUOTENAME(t.name), ' TO ', QUOTENAME(m.name), ' AS ', QUOTENAME(g.name), ';')
    ELSE
        CONCAT(p.state_desc, ' ', CAST(p.permission_name AS varchar(80)) COLLATE SQL_Latin1_General_CP1_CI_AS, ' TO ', QUOTENAME(m.name), ' AS ', QUOTENAME(g.name), ';')
    END AS sqlcmd
  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'
    LEFT OUTER JOIN sys.database_principals i ON p.major_id = i.principal_id
    LEFT OUTER JOIN sys.types t ON p.major_id = t.user_type_id
    LEFT OUTER JOIN sys.database_principals g ON p.grantor_principal_id = g.principal_id
  ORDER BY CASE p.class_desc WHEN 'DATABASE' THEN 1 WHEN 'DATABASE_PRINCIPAL' THEN 2 WHEN 'SCHEMA' THEN 3 WHEN 'TYPE' THEN 4 ELSE 5 END, object_name, o.name, p.permission_name;