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 |
|
ReallyWildStuff
Starting Member
1 Post |
 Posted - 2005-08-15 : 12:32:44
|
| newbie DBA...didn't set this MS SQL server up, not sure about anything relating to itMS SQL 2000 8.00.760 (SP3) running on W2K SP4Short version is we try to change permissions on a particular table within a particular database, Apply the changes, go back and look at the changes and they're still there, but overnight they revert back to what they were before.We log onto the box directly as Administrator and open SQL Server Enterprise Manager (MMC 1.2) and open up the Server, then "Databases", then "database name", then "Tables", then right click on the table in question, select "Properties", then click "Permissions" button...Permissions is set to show "List all users/user-defined database roles/public" and shows a particular group (or user) that we want to specify access for, we put a checkmark in (for example) "Select" and "Insert" columns and green check marks are there, we press Apply, back out of it and then go back in and everything is as we set it.But next day, it's back the way it was at first. There must be something that's running at night that's...resetting permissions? Seems strange to me, don't really know where to look.Thanks,ReallyWildStuff |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-15 : 12:45:56
|
quote:
Originally posted by ReallyWildStuff
newbie DBA...didn't set this MS SQL server up, not sure about anything relating to itMS SQL 2000 8.00.760 (SP3) running on W2K SP4Short version is we try to change permissions on a particular table within a particular database, Apply the changes, go back and look at the changes and they're still there, but overnight they revert back to what they were before.We log onto the box directly as Administrator and open SQL Server Enterprise Manager (MMC 1.2) and open up the Server, then "Databases", then "database name", then "Tables", then right click on the table in question, select "Properties", then click "Permissions" button...Permissions is set to show "List all users/user-defined database roles/public" and shows a particular group (or user) that we want to specify access for, we put a checkmark in (for example) "Select" and "Insert" columns and green check marks are there, we press Apply, back out of it and then go back in and everything is as we set it.But next day, it's back the way it was at first. There must be something that's running at night that's...resetting permissions? Seems strange to me, don't really know where to look.Thanks,ReallyWildStuff
I'll try to give you a nudge in some possible directions so you can figure it out yourself and learn as a DBA.  Maybe there's an SP out there that's doing grant/revoke statements. Try looking in syscomments. Maybe you could setup profiler to audit for grant/revoke statements....DanielSQL Server DBAwww.dallasteam.com |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-08-15 : 13:06:08
|
| I think this could do it too ...create table stuff(reallywild as int)grant select on stuff to DBA_Danselect * into stuff_temp from stuffdrop table stuffexec sp_rename 'stuff_temp','stuff'Jay White |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-15 : 13:54:55
|
quote:
Originally posted by Page47
I think this could do it too ...create table stuff(reallywild as int)grant select on stuff to DBA_Danselect * into stuff_temp from stuffdrop table stuffexec sp_rename 'stuff_temp','stuff'Jay White
No. Once you drop the object the permissions go with it. The object permissions are associated with the objectid and even if they managed to get orphaned they would not be associated with the other objects objectid.  DanielSQL Server DBAwww.dallasteam.com |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-08-15 : 13:58:35
|
quote:
Originally posted by SQLServerDBA_DanNo. Once you drop the object the permissions go with it. The object permissions are associated with the objectid and even if they managed to get orphaned they would not be associated with the other objects objectid. DanielSQL Server DBAwww.dallasteam.com
What I meant by "I think this could do it too" was that in addition to someone manually revoking permissions, you can also lose permissions if somewhere the table is dropped and recreated. So in addition to searching syscomments for revokes, you should search for drops ...Jay White |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-15 : 14:11:40
|
quote:
Originally posted by Page47
quote:
Originally posted by SQLServerDBA_DanNo. Once you drop the object the permissions go with it. The object permissions are associated with the objectid and even if they managed to get orphaned they would not be associated with the other objects objectid. DanielSQL Server DBAwww.dallasteam.com
What I meant by "I think this could do it too" was that in addition to someone manually revoking permissions, you can also lose permissions if somewhere the table is dropped and recreated. So in addition to searching syscomments for revokes, you should search for drops ...Jay White
Hehe. Yeah I was a bit one-track minded on that comment.  I was only thinking about adding permissions. But in this case the drop would in fact loose permissions.I've seen many instances where 3rd party applications will have a grant statement at the bottom of their SP's.DanielSQL Server DBAwww.dallasteam.com |
|
|
|
|
|
|
|
|
permissions keep getting reset