asked on SQL Server 2005 users lost their security permissions/rights
befor few days, some users lost their security permissions/rights. We have no clue who did this. Can someone guide me how can i view which user modified permissions of the other users or how can we enable auditing/logging to check who is modifiying the permissions.
Microsoft SQL Server 2005
Last Comment
nmcdermaid
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Did you by any chance restore any databases? That can overwrite permissions at the database level.
ASKER
Yes We restored a databases. and after that this happened. But user permissions were on SQL Server Level. How these can be override if we restore a database.
How can we set "user permissions modification" logging. tell me in easy way. i am newbe
How can we set "user permissions modification" logging. tell me in easy way. i am newbe
Security defined at SQL Server level is:
1. Can I log into the SQL Server or not
2. What server roles do I belong to (if sysadmin then I can do anything)
Security defined at the database level is
1. Do I have access to a database
2. What kind of database access is it - read only
3. What objects can I see and what can I do with them.
So are you saying that a SQL Server Login was removed or their server role was changed? If so then yes, the database restore has nothing to do with it.
To put it another way, a database restore won't affect the 'Server Roles' tab but it can affect the 'User Mapping' tab
1. Can I log into the SQL Server or not
2. What server roles do I belong to (if sysadmin then I can do anything)
Security defined at the database level is
1. Do I have access to a database
2. What kind of database access is it - read only
3. What objects can I see and what can I do with them.
So are you saying that a SQL Server Login was removed or their server role was changed? If so then yes, the database restore has nothing to do with it.
To put it another way, a database restore won't affect the 'Server Roles' tab but it can affect the 'User Mapping' tab
ASKER
How can we set "user permissions modification" logging. tell me in easy way
I suggest you follows rrjegan17's instructions (reproduced):
If you want to monitor it in Future, then Create a Profiler Trace and capture all sorts of Security Events. Steps to do it are:
1. Create a New Profiler.
2. Click Event Selection Tab.
3. Click Show All events.
4. Under Security Audit, Choose all sorts or events to be captured.
5. Save it in either a table or file.
If you want to monitor it in Future, then Create a Profiler Trace and capture all sorts of Security Events. Steps to do it are:
1. Create a New Profiler.
2. Click Event Selection Tab.
3. Click Show All events.
4. Under Security Audit, Choose all sorts or events to be captured.
5. Save it in either a table or file.
Note that if your issue is due to a db restore then capturing security events in profiler is not going to help. So you might want to resilve whether that is the issue.