Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
 Posted - 2006-03-23 : 14:07:07
|
| hello everyone,am not sure if this is the right forum, but here goes.We have a couple of clients we support running MS SQL, and as a rule, all db access is by SP and select statements.However, we noticed earlier today, that a newly created account was empty. All columns except the users columns had been deleted.1. Is there a way of checking which user account deleted the columns ?2. Or finding out what went wrong ?This has never happened beforeAfrika |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-03-23 : 15:42:33
|
Afrika, how do you delete a column?alter table x drop colum y ???are the users db_owner?if only running select + sp's I suggest you only give the users appropriate select and execute permissions.to find out who did something, I think you need to get som log-reading tool, although i'm not sure if you can determine the user who did the operation.you can also try out the undocumented:dbcc log('<dbname>',[0,1,2,3,4])to read the transaction log, but a tool would be easier  rockmoose |
 |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2006-03-25 : 16:13:55
|
| >>Afrika, how do you delete a column?Sorry my mistake.I meant deleted the data, was really shocked to see all data gone.Well, we restored the backup locally then exported it to the remote server.All users are db owners, what we did was to deny insert, update and delete to all tables and use only SP access to tables.Am looking at the dbcc in BOL.Thanks for the adviceafrika |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-25 : 19:17:04
|
| "All users are db owners, what we did was to deny insert, update and delete to all tables and use only SP access to tables."If they are db_owners, they can give themselves any permission they want. They can also create a SP to do what they want.It would be better security to setup roles that allow access to objects, grant the necessary permissions on objects to the roles, add the users to the necessary roles, and remove the users from the db_owner role.CODO ERGO SUM |
|
|
|
|
|
|
|
|
database empty