1

SQL2012.

I have a user and only want them to have select access to a single view and nothing else.

I tried to create my own schema containing the view and deny access to dbo but it seems it doesn't work as I would have expected it to. I can still run queries against tables even though I can't seem them in SSMS.

CREATE SCHEMA vl
CREATE VIEW vl.vw_Search AS SELECT 'This is a test' AS RESULT
GRANT select ON vl.vw_Search TO vlu
DENY SELECT On SCHEMA::sys To vlu
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To vlu
DENY INSERT, UPDATE, DELETE ON schema::dbo to vlu

DENY VIEW ANY DATABASE TO vlu
DENY VIEW ANY DEFINITION TO vlu

I can also still access system views which I also don't want the user to be able to do.

0

Use this code to see who has access to what:

;WITH    perms_cte as
(
        select USER_NAME(p.grantee_principal_id) AS principal_name,
                dp.principal_id,
                dp.type_desc AS principal_type_desc,
                p.class_desc,
                OBJECT_NAME(p.major_id) AS object_name,
                p.permission_name,
                p.state_desc AS permission_state_desc
        from    sys.database_permissions p
        inner   JOIN sys.database_principals dp
        on     p.grantee_principal_id = dp.principal_id
)
--users
SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM    perms_cte p
WHERE   principal_type_desc <> 'DATABASE_ROLE'
UNION
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM    perms_cte p
right outer JOIN (
    select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
    from    sys.database_role_members rm
    INNER   JOIN sys.database_principals dp
    ON     rm.member_principal_id = dp.principal_id
) rm
ON     rm.role_principal_id = p.principal_id
order by 1;

It is likely the account in question is a member of some role, such as db_datareader which is giving it the ability to query other tables. By default, accounts do not have the ability to access anything.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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