Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A permissions issue with one table Expand / Collapse
Author
Message
Posted Thursday, August 29, 2013 4:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 169, Visits: 623
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!
Post #1489951
Posted Thursday, August 29, 2013 4:47 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:12 PM
Points: 867, Visits: 7,563
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
Post #1489965
Posted Thursday, August 29, 2013 5:52 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1489973
Posted Friday, August 30, 2013 3:31 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 AM
Points: 823, Visits: 753
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
Post #1490319
Posted Friday, August 30, 2013 4:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 169, Visits: 623
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!
Post #1490327
Posted Friday, August 30, 2013 4:18 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 11:24 AM
Points: 153, Visits: 981
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
Post #1490328
Posted Friday, August 30, 2013 4:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 169, Visits: 623
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!
Post #1490330
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse