Select Permission Error On A Insert Statement

Question

Select Permission Error On A Insert Statement

jude.pieries

Default port

Points: 1438
More actions
July 28, 2015 at 11:23 pm

#306198

Any of you have run to a select permission error when attempting insert data to a table. received the following error
Msg 229, Level 14, State 5, Line 11
The SELECT permission was denied on the object 'tableName', database 'DBname', schema 'Schema'.
Few things to note
- There are no triggers depending on the table
- Permissions are granted at a roll level which is rolled down to the login
- The test environments have the same level of permission which works fine.
Any advice would be appreciated 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

jude.pieries (7/28/2015)
Any of you have run to a select permission error when attempting insert data to a table. received the following error
Msg 229, Level 14, State 5, Line 11
The SELECT permission was denied on the object 'tableName', database 'DBname', schema 'Schema'.
Few things to note
- There are no triggers depending on the table
- Permissions are granted at a roll level which is rolled down to the login
- The test environments have the same level of permission which works fine.
Any advice would be appreciated 🙂

Can you post the insert statement? Maybe there is a trigger on the table you trying to insert into? Without more details than this nobody can do much here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jude.pieries Default port Points: 1438 More actions · Answer 2

Hi Sean

Thanks for responding,

1.) The query is a pretty straight forward LLBEGEN query

ie

declare @Sequence int ,

@A1 uniqueidentifier = newguid() -- can be any guide value

,@A2 varchar(100) = 'BS'

,@A3 uniqueidentifier = newguid() -- can be any guide value

,@A4 int = 5

,@A5 datetime = getutcdate()

begin

INSERT INTO [dbo].[Statistics] ([Col1], [Col2], [Col3], [Col4], [Col5)

VALUES (@A1, @A2, @A3, @A4, @A5);SELECT @Sequence=SCOPE_IDENTITY()

-- rollback

FYI , the Scope_identity() doesn't contribute to the error 🙂

2.) yes, i had already looked for triggers and found none

SELECT

TAB.name as Table_Name

, TRIG.name as Trigger_Name

, TRIG.is_disabled

FROM [sys].[triggers] as TRIG

inner join sys.tables as TAB

on TRIG.parent_id = TAB.object_id

where TAB.name = 'tablename'

ARC211 Ten Centuries Points: 1283 More actions · Answer 3

are you using always on and failover cluster?

are db_datareader and db_datawriter set on the user?

jude.pieries Default port Points: 1438 More actions · Answer 4

- Yep, this is popping up on a db that's participating on AG. I am curious to know why you asked this question. I wouldn't have thought this could have contributed, but there is always some wired shit

- The application user inherits permission from the DB role which is granted the desired permissions

ARC211 Ten Centuries Points: 1283 More actions · Answer 5

I'm sure you have already looked into it, but I have made the mistake in the past where I would try to update on the node were in the DB in question was not a primary on that node.

ARC211 Ten Centuries Points: 1283 More actions · Answer 6

I tested it and it was a different error.... See attached