0

I would like to adjust the permissions of a SQL Server table so that only SELECT and INSERT commands are possible for users. The execution of all other commands (e.g. UPDATE, DELETE, ...) should not be possible.

I have tried the following:

USE MyDB
GO

CREATE TABLE dbo.TestTable
(
    colA INT,
    colB INT
);

CREATE ROLE my_role;

GRANT SELECT, INSERT ON OBJECT::dbo.TestTable TO my_role;
DENY UPDATE, DELETE ON OBJECT::dbo.TestTable TO my_role;

ALTER ROLE my_role ADD MEMBER user1;

But the permissions didn't seem to have changed. Everything is still possible. I've read, that Ownership chains can be the cause, but even if I create a new schema with public as owner and create the table on this schema it's the same like above.

How can I set table permissions that will work?

5
  • 4
    Whats SQL Server 15? You need to run select @@version
    – Dale K
    Aug 10, 2023 at 1:01
  • 2
    Who is the owner of TestTable? Is it my_role? Aug 10, 2023 at 1:15
  • 2
    Is the user also in the db_owner role members? Or other roles that grant delete and update privileges? Aug 10, 2023 at 5:41
  • 1
    Sounds like, to me, that the USER is part of other database ROLEs, has been granted other permissions explicitly, or they are a sysadmin (I really hope not the latter).
    – Thom A
    Aug 10, 2023 at 7:54
  • The SQL Server version is "Microsoft SQL Server 2019 - 15.0.2101.7". The Table owner is dbo (Query was: SELECT p.name AS table_owner FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.database_principals p ON s.principal_id = p.principal_id WHERE t.name = 'TestTable';) The user is not sysadmin, and it has only the role "my_role" (SQL: EXEC sp_helpuser 'user1')
    – nathyxy
    Aug 10, 2023 at 9:35

1 Answer 1

0

user1 got the sysadmin role through some weird Active Directory groups....

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.