January 31, 2008 at 4:36 am
It seems someone somewhere somehow has deleted or reset all the permissions on a DB role I created which of cause caused lots of "Update Permission was denied on XXX for user XXX" errors on the website.
I don't know how it happened and no-one else seems to have any idea either. The role still appears under the list of database roles but when you click on the securables nothing was ticked for any of the tables in the DB.
I have just gone through each of the 50 or so tables again granting the correct select/update/delete/insert permissions etc which has fixed the web site erroring but how do I script all these out so that if it happens again I can just run the script.
E.G I want to be able to loop through each item (table/proc) that has been granted some kind of permission to the role and output a long list of statements of the following:
grant select,update,insert,delete on object::SOME_TABLE to MY_ROLE
So I could just re-run this script in future.
Thanks
January 31, 2008 at 8:23 am
Try this. I'll bet you can use it to create scripts.
declare @username as varchar(50)
set @username = 'your role'
select O.name, U.name,permission_name from sys.database_permissions
join sys.sysusers U on grantee_principal_id = uid
join sys.sysobjects O on major_id = id
where U.name = @username
order by O.name
Greg
January 31, 2008 at 8:33 am
Maybe the error was caused by someone deleting the role and then creating it again.
Seems more likely than someone accidentally revoking permission on all tables to just that role.
January 31, 2008 at 3:31 pm
Cheers for that SQL that will work a treat to output the correct GRANT permissions.
As for the role being deleted and re-created again, maybe but no-one will own up to it! I just
need to be prepared in case the blindfolded SQL trainees start practising their delete statements again.
January 31, 2008 at 4:17 pm
Rob Reid (1/31/2008)
Cheers for that SQL that will work a treat to output the correct GRANT permissions.As for the role being deleted and re-created again, maybe but no-one will own up to it! I just
need to be prepared in case the blindfolded SQL trainees start practising their delete statements again.
You might consider taking away their permission to make those changes. Just like not letting a 4-year drive your car.
February 1, 2008 at 4:35 am
Well I'm not in a position to do that am I seeing I'm only the developer! Maybe if it was my system or I had control of who else used our development server I would consider doing that.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply