Does a Job failed result in change in permissions for a Table?

Question

Does a Job failed result in change in permissions for a Table?

srikantzs

SSC Eights!

Points: 818
More actions
March 17, 2010 at 12:54 pm

#142070

Hello guys,
I had a job to alter data type of the primary key in a table.But that job failed and after that when I see the permissions on that table are revoked i.e.whenever I do a select on that table it returns
"The SELECT permission was denied on the object 'table name ', database 'DB Name', schema 'dbo'.
Does a job failure result in change of permissions?
Please give me all the info of the cases where permission might be revoked without manually running the revoke command.
Thanks,
Srikanth

Viewing 13 posts - 1 through 13 (of 13 total)

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

TheRedneckDBA SSChampion Points: 14003 More actions · Answer 1

srikantzs (3/17/2010)
Hello guys,
I had a job to alter data type of the primary key in a table.But that job failed and after that when I see the permissions on that table are revoked i.e.whenever I do a select on that table it returns
"The SELECT permission was denied on the object 'table name ', database 'DB Name', schema 'dbo'.
Does a job failure result in change of permissions?
Please give me all the info of the cases where permission might be revoked without manually running the revoke command.
Thanks,
Srikanth

I wouldn't think changing a data type would affect permissions. But exactly how are you changing the data type? When you say you had a "job" to do it, to you mean you just ran a script, or you scheduled a SQL Server Agent job to do it?

The Redneck DBA

srikantzs SSC Eights! Points: 818 More actions · Answer 2

I created a job and put T-SQL statements in it

alter table

alter column

and the job failed.

TheRedneckDBA SSChampion Points: 14003 More actions · Answer 3

Not sure how that would change permissions. What reason did it give for failing?

The Redneck DBA

srikantzs SSC Eights! Points: 818 More actions · Answer 4

To just check the Permission set on that table,

I used a Table valued function,but it didn't return any rows.

SELECT *

FROM fn_my_permissions('tablename', 'OBJECT')

0 rows returned

which tells me the permissions are messed up somehow.

srikantzs SSC Eights! Points: 818 More actions · Answer 5

And one more thing,

When I use the select query on that table in a UDF,it executes with out any error,but if I use the same select query separately it fails stating permissions error.

TheRedneckDBA SSChampion Points: 14003 More actions · Answer 6

srikantzs (3/17/2010)
To just check the Permission set on that table,
I used a Table valued function,but it didn't return any rows.
SELECT *
FROM fn_my_permissions('tablename', 'OBJECT')
0 rows returned
which tells me the permissions are messed up somehow.

Not sure how much I'd read into that though...I think that only gives explicitly granted permissions, and won't show permissions due to server/database roles. (i.e., if I run it as a sysadmin, it returns nothing for me too).

The Redneck DBA

srikantzs SSC Eights! Points: 818 More actions · Answer 7

And one more thing,

When I use the select query on that table in a UDF,it executes with out any error,but if I use the same select query separately it fails stating permissions error.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 8

Do a sp_helpuser against ur db and see if ur login comes in output with required permissions.

MJ

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

Srikanth ,

can you post your script which is used in job, i guess it needs some modification

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

srikantzs SSC Eights! Points: 818 More actions · Answer 10

Hi all,Thanks for your replies

Let me tell you guys the exact issue I have.

We have a table in the SQL server which isn't visible to developers.When I do a select on that table it gives an error

"The SELECT permission was denied on the object 'table1', database 'DBname', schema 'dbo'." This says the table exists but not enough permissions.But when we contacted our DBA's they say permissions are set properly and they didn't make any changes.No one is exactly sure of what went wrong regarding that table.

We did a work around by creating another table and loading data again which isn't the answer for that issue.

Can you tell what might have caused this?

TheRedneckDBA SSChampion Points: 14003 More actions · Answer 11

So you're in a development position and not in a DBA position? If that's the case, I (personally) wouldn't be spending much time figuring it out and make the DBA fix it! 🙂

The Redneck DBA

srikantzs SSC Eights! Points: 818 More actions · Answer 12

Yes,I am in developer position,but wanted to know the reason from a DBA perspective.....Thanks for the help ...Do you see any reason for that to happen?