June 19, 2010 at 4:40 pm
Hello all,
I have a strange error here.
I am a SQLDBA and I am sys admin on my server.
I am trying to insert some rows into a table and its giving me error saying that
Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'table_name', database 'db_name', schema 'dbo'.
Can anyone help me on how to debug this error or Do I need to assign some special permissions on this table or what to look into while debugging this error.
June 19, 2010 at 4:43 pm
It seems that the login you are using to run the insert does not have adequate permissions in the database. Are you sure you are logging into SQL Server with sysadmin?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 19, 2010 at 7:09 pm
Yes, I am the sys admin and I have permissions on all the tables.
Do you think the owner who created this table can avoid sysadmin to modify this table?
June 19, 2010 at 8:28 pm
It isn't possible.
Can you select or update on the table in question?
edit: Fixed typo.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 19, 2010 at 9:39 pm
yes, i can select but iam not able to insert new records, think iam not able to update also....
June 19, 2010 at 11:16 pm
Have you tried to re-grant yourself the permissions?
http://msdn.microsoft.com/en-us/library/ms187965.aspx
The link provided will show how to grant permissions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 20, 2010 at 2:19 am
- are there any triggers on the object (that may prevent inserts ...)
- how do you actually insert ? using an sproc ? (check if it uses the execute as clause)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 20, 2010 at 9:02 am
ALZDBA (6/20/2010)
- are there any triggers on the object (that may prevent inserts ...)- how do you actually insert ? using an sproc ? (check if it uses the execute as clause)
Very likely causes.
Thanks Alzdba for pointing that out.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 20, 2010 at 9:35 am
striker-baba (6/19/2010)
Do you think the owner who created this table can avoid sysadmin to modify this table?
No. Sysadmins bypass all permission checks, hence nothing can be denied to a sysadmin.
What's the output of the following?
SELECT logins.name, roles.name
FROM sys.server_principals logins
INNER JOIN sys.server_role_members rm ON logins.principal_id = rm.member_principal_id
INNER JOIN sys.server_principals roles ON rm.role_principal_id = roles.principal_id
WHERE logins.name = SUSER_SNAME()
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2010 at 10:15 am
Are you calling a stored procedure or running ad-hoc insert statements?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2010 at 12:07 am
Yes Sir,
I ran the above mentioned script and I am the sysadmin for the server.
And I am trying to insert data through a linked server from a remote test server into my prod server.
this is a Master table which we usually use for archiving data.
But this is the first time I am using this table for archiving since I am a new DBA.
but I did archive on other master tables and did not face any problem, but this is the only table I am having problem with.
or can you suggest me the way I can inherit permissions for this table or any other way of inserting the data into table.
I really appreciate all of you guys for following up on my issue
thanks very much for the support from the forum.
June 21, 2010 at 12:09 am
We dont have any triggers on the table nor any constraints to prevent from inserting.
I also tried to ressign permissions but still having the same problem.
June 21, 2010 at 12:19 am
Check the linked server definitions as they may have their own security setting to get to the linked server !
One option may be to adjust Gails query to
SELECT logins.name, roles.name
FROM Linkedservername.master.sys.server_principals logins
INNER JOIN Linkedservername.master.sys.server_role_members rm ON logins.principal_id = rm.member_principal_id
INNER JOIN Linkedservername.master.sys.server_principals roles ON rm.role_principal_id = roles.principal_id
WHERE logins.name = SUSER_SNAME()
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 21, 2010 at 1:48 am
Check for server trigger by running
SELECT *FROM sys.server_triggers
June 21, 2010 at 2:10 am
striker-baba (6/21/2010)
And I am trying to insert data through a linked server from a remote test server into my prod server.
It would have been useful if you'd mentioned that in your initial post. As soon as you're working with a linked server, your permissions on the server are irrelevant, what's important is the security settings on the linked server
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply