August 29, 2013 at 4:23 pm
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!
August 29, 2013 at 4:47 pm
Did you, by any chance, explicitly deny insert to 'public'? What error message do you get?
August 29, 2013 at 5:52 pm
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
August 30, 2013 at 3:31 pm
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?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 30, 2013 at 4:09 pm
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!
August 30, 2013 at 4:18 pm
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
August 30, 2013 at 4:26 pm
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!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply