0

I am using SQL Server 2019. I have gone through some questions here and not found an answer. I am trying to not to show some tables (with a certain Schema e.g. Structure) for a SPECIFIC user in the Object Explorer in SSMS.

Here is what I have done:

  1. I created a role called db_views and added the user to it
  2. Denied View Definition for Schema:: Structure to [db_views]
  3. Denied Select On Schema as well

The user is still able to see all tables (bad) in the Object Explorer but can not open them (good).

Is there any way I can get SSMS to not show the tables? or is that not a possibility?

1
  • 1
    I don't believe this is possible. It sounds like you've done everything correct, and as far as SQL Server is concerned, any user in the db_views role won't be able to view the definition of objects in that schema, and won't be able to select from objects in that schema.
    – J.D.
    Oct 28, 2021 at 0:14

1 Answer 1

0

Here are a few queries I sometimes use to figure out why permissions do not work as I think they should. I set the <database name> to the one I want to test against and set the <login name> to the user I am testing then run the whole script to read the output.

That may help figure out if the user has permissions other than those specifically granted. One reason is the user may be part of a group/role that may grant permissions that are not explicitly denied elsewhere.

-- Set target database
USE <database_name> -- replace with actual database name
GO
-- This declares the variables used in the queries below.
DECLARE @PrincipalName sysname = '<login_name>'-- replace with actual login name
    , @DatabasePrincipalID int
    , @ServerPrincipalID int
    , @DaValue nvarchar(100);
SET @DatabasePrincipalID = (SELECT DATABASE_PRINCIPAL_ID(@PrincipalName));
SET @ServerPrincipalID = (SELECT SUSER_ID(@PrincipalName)); 

-- Comment out if @PrincipalName is for a SQL Authenticated User
 -- otherwise show if user is part of any groups
EXEC xp_logininfo @PrincipalName

-- Display the Server Principal ID
SELECT @ServerPrincipalID as ServerPrincipalID

-- Display the Database Principal ID
SELECT @DatabasePrincipalID as CurrentDatabasePrincipalID

-- Display database permissions assigned to the user
SELECT class_desc as ObjectHoldingPermissionPermission
    , permission_name as PermissionName
    , state_desc as Permission
    , grantee_principal_id as DatabasePermissionAssignedTo
FROM sys.database_permissions
WHERE grantee_principal_id = @DatabasePrincipalID

-- Show if database principal is member of database roles
SELECT sp.name as MemberName
    , sp.type_desc as MemberType
    , sp2.name as RoleName
    , sp2.type_desc as RoleType
    , sp2.is_fixed_role as IsFixedRole
    , IS_ROLEMEMBER(sp2.name, @PrincipalName) as IsPrincipalDatabaseRoleMember
FROM sys.database_role_members as srm
LEFT JOIN sys.database_principals as sp
    ON srm.member_principal_id = sp.principal_id
LEFT JOIN sys.database_principals as sp2
    ON srm.role_principal_id = sp2.principal_id
WHERE sp.principal_id = @DatabasePrincipalID

-- Show if server principal is member of server roles
SELECT sp.name as MemberName
    , sp.type_desc as MemberType
    , sp2.name as RoleName
    , sp2.type_desc as RoleType
    , sp2.is_fixed_role as IsFixedRole
    , IS_SRVROLEMEMBER(sp2.name, @PrincipalName) as IsPrincipalServerRoleMember
FROM sys.server_role_members as srm
LEFT JOIN sys.server_principals as sp
    ON srm.member_principal_id = sp.principal_id
LEFT JOIN sys.server_principals as sp2
    ON srm.role_principal_id = sp2.principal_id
WHERE sp.principal_id = @ServerPrincipalID

-- Display the database principals that has the user as a member
SELECT principal_id
    , name
    , type_desc
    , IS_ROLEMEMBER(name, @PrincipalName) as IsPrincipalDatabaseRoleMember
FROM sys.database_principals
WHERE type IN ('R','G')
    AND IS_ROLEMEMBER(name, @PrincipalName) = 1
ORDER BY IsPrincipalDatabaseRoleMember desc, type, name

-------- The next two queries use STRING_AGG and STRING_SPLIT --------
-- Aggregate all the database principals user is member of and set to variable
SELECT @DaValue = STRING_AGG(principal_id, ',')
FROM sys.database_principals
WHERE type in ('R','G')
    AND IS_ROLEMEMBER(name, @PrincipalName) = 1

-- Display all objects that have permissions assigned to user
SELECT dp.permission_name as PermissionName
    , dp.state_desc as PermissionState
    , dp.class_desc as PermissionClass
    , o.name as ObjectName
    , schema_name(o.schema_id) as SchemaName
    , o.type_desc as ObjectType
FROM sys.database_permissions as dp
LEFT JOIN sys.objects as o
    ON o.object_id = dp.major_id
WHERE dp.grantee_principal_id IN (SELECT VALUE FROM STRING_SPLIT(@DaValue, ','))
    AND dp.major_id >= 0 -- major id less than zero are system objects. comment out if want to see all objects
ORDER BY dp.grantee_principal_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.