Restricting table permissions for one user but I'm unable to alter table, why?

Other SQL Server Topics
, , ,
1

Hello,

I apologize for not being more succinct with the Subject title. I couldn't think of the proper phrase for this problem. It goes like this..

I created a new table. Let's call the table, FooBar. Normally, I don't need to apply any user permissions. In this one case, I do. I created a user named WebUserFooBar. I granted permissions for this user to execute INSERT and SELECT statements on table FooBar. WebUserFooBar has no access to any other tables. He is part of one server role, which only has CONNECT permissions (I didn't want the user to have any roles, but the system wouldn't let me add a new user without attaching him to at least one server role). When he logs in via MSQLSMS, he sees this one table. This is good because this user will be performing insert and select statements via server-side code.

Now, I can see all tables, including FooBar, in MSQLSMS and perform all kinds of operations (i.e. delete, insert, select, update) on this table.

Now, I want to add a check constraint on a column in the FooBar table. However, when I try to save, I get an error:

'FooBar' table
- Unable to add constraint 'CK_FooBar'.  
The ALTER TABLE statement conflicted with the CHECK constraint "CK_Foobar".

What's the deal? I can freely add or delete records in this table via MSQLSMS -- I'm logged in with Windows authentication. So, why can't I alter the table?

I am grateful for any help I receive. Thank you very much.

2

This probably has nothing to do with the permissions you granted to a user. It usually happens when the data existing in the table does not meet the check constraint. The error message also should usually give you some additional information such as which columns are causing the problem etc.

1 Like
3

Hello James,

Thank you so much. You were right. It wasn't a permissions issue. It was an issue with existing data. I suppose when I create the constraint, I can mark NO for checking existing data.

Thanks again.