SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A permissions issue with one table


A permissions issue with one table

Author
Message
WendellB
WendellB
Right there with Babe
Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)

Group: General Forum Members
Points: 756 Visits: 1755
We have a SQL Server 2008 installation where we have one table that users cannot do an insert to unless they are granted the SysAdmin role. It appears that it belongs to the dbo schema, and should allow any user that belongs to that schema and is a member of the db_owner group to do inserts. We've even tried giving the user explicit permissions for that specific table to no avail. All other tables in the database are just fine. Any suggestions as to the appropriate course of action? We don't really want any user that does data entry to have the SysAdmin role.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1558 Visits: 8586
Did you, by any chance, explicitly deny insert to 'public'? What error message do you get?



And then again, I might be wrong ...
David Webb
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (10K reputation)

Group: Moderators
Points: 10232 Visits: 1917
Check what David said.

A member of db_owner, except for dbo, will honor a DENY on a table. Members of the sysadmin role map in as dbo, which effectively ignores the permissions.

Execute this query in the DB to see what permissions are assigned against the table:

SELECT t.name AS 'Table', u.name AS 'User_or_Role', dp.state_desc, dp.permission_name 
FROM sys.database_permissions AS dp
JOIN sys.tables AS t
ON dp.major_id = t.object_id
JOIN sys.database_principals AS u
ON dp.grantee_principal_id = u.principal_id
WHERE dp.class = 1
AND t.name = '**Your Table Here**';



K. Brian Kelley
@‌kbriankelley
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2113 Visits: 872
In addition to the other posts, permit me to point that one important piece is missing from your post: what error do they get?

And, for that matter, do they insert directly or through a strored procedure?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
WendellB
WendellB
Right there with Babe
Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)

Group: General Forum Members
Points: 756 Visits: 1755
This is actually a linked table with a Microsoft Access front-end using ODBC, and in typical ODBC fashion, it gives an obscure error code that indicates the user does not have the permissions necessary to insert a record in that table. I didn't actually record the error number - sorry, and I should have also recorded the actual error message text, and I didn't do that either. Unfortunately the system is at a remote site where it is very difficult to try to replicate the problem and get the actual message, but I will do that if the steps suggested above don't resolve the issue. Thanks.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Andreas.Wolter
Andreas.Wolter
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1056
WendellB (8/30/2013)
This is actually a linked table with a Microsoft Access front-end using ODBC, and in typical ODBC fashion, it gives an obscure error code that indicates the user does not have the permissions necessary to insert a record in that table. I didn't actually record the error number - sorry, and I should have also recorded the actual error message text, and I didn't do that either. Unfortunately the system is at a remote site where it is very difficult to try to replicate the problem and get the actual message, but I will do that if the steps suggested above don't resolve the issue. Thanks.


This changes about everything:
check the whole line from you local account - linked Server Login mapping - up to the permissions of that mapped account.

I would not be surprised, if there was a mapping for sysadmins only...

Andreas

---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
WendellB
WendellB
Right there with Babe
Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)

Group: General Forum Members
Points: 756 Visits: 1755
As a matter of fact, DENY had be put on the public role. I won't have an opportunity to test it until Tuesday, but I suspect that was the issue. So thanks for your suggestion David Webb, and to K. Brian Kelley for the T-SQL which made identifying the permissions on that table an easier task. In SSMS the public role was at the very bottom and I never thought to check it.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search