I have had this happen to me on numerous occassions. We have a particular database that is currently running SQL 7. For some reason, out of the clear blue (it seems) at times, a particular table will lose it's permissions. In other words, when you double click on a table and choose permissions, the select, delete, etc. permissions that were there before are all gone. Anyone else experience this? The permissions were definately there before, because we found out when one day the users started calling wondering what happened. Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Anyone know why tables lose permissions?
- Thread starter fjc718
- Start date
tlbroadbent
MIS
If a table, view or stored procedure is dropped and recreated, the old permissions are lost. If a user is dropped and added, the permisisons will be lost. Otherwise, the most likely cause would be a stray SQL script, containing Revoke or Deny statements, that is somehow executed. Terry
------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.
------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.
this has happened to me a number of times, and without fail every time, there is a logical explanation... as noted above, someone with the correct privilidges is likely accessing the system or running a script that is dropping or doing something to drop your permissions.
one think you can do is to turn on auditing, and you will see all of the logins in your errorlog.... this might give you a tip.
my motto with this type of error is that the computer is rarely wrong.
Paul
one think you can do is to turn on auditing, and you will see all of the logins in your errorlog.... this might give you a tip.
my motto with this type of error is that the computer is rarely wrong.
Paul
- Thread starter
- #5
to get the errorlog:
1. in EM, go to managment and slect the logs
2. go to \mssql7\log this is the default location and look for 'errorlog' the .1, .2 .... are older verions
3. from query analyzer enter sp_readerrorlog (you need rights) and it will be returned to you.
to turn on the auditing, you can do it in the 'properties' panel (I don't have my EM here, but it would likely be in 'security' tab'
Paul
1. in EM, go to managment and slect the logs
2. go to \mssql7\log this is the default location and look for 'errorlog' the .1, .2 .... are older verions
3. from query analyzer enter sp_readerrorlog (you need rights) and it will be returned to you.
to turn on the auditing, you can do it in the 'properties' panel (I don't have my EM here, but it would likely be in 'security' tab'
Paul
- Thread starter
- #7
Yeah, I turned on the log...it's in the properties, security tab. Man, I lost more permissions today..this is really odd. It's not a stray user, guaranteed. The org. is not that big and I have double checked security. We do have a customized VB app running on the front end that may be executing some scripts....Im beginning to suspect that the problem lies there. I will monitor the logs and see what's up. Tx.
- Status
Similar threads
