SQL Server Performance Forum – Threads Archive

problem with roles

hello,
I have crated a "SQLStoredProcedureExecutor" role, to which I have granted execute permissions to all procedures, after some days when I check that role and look at the permissions that has, I see that almost all of procedures are not granted to that role.
Can Someone Explain me, why this is happening?

its possible that the procs are being dropped and recreated, but the recreation script is not restoring the permissions. If you script an object out remember to tick ‘include object level permissions’. Either that or use ALTER PROCEDURE instead of DROP/CREATE

Is this a development database? If someone has been making changes to the stored procedures with scripts that do a DROP PROCEDURE before the CREATE PROCEDURE, then all permissions on the procedure are dropped as well. They must be granted again after the CREATE PROCEDURE statement. If you run the scripting wizard in EM or QA, note that you must set them to script permissions along with the object. Alternatively, use ALTER PROCEDURE: this will leave the permissions on the object untouched. A disadvantage of using ALTER PROCEDURE is that the Create Date for the object doesn’t change either, so you have to include some comments within the procedure to document the changes, and when they were done (always a good idea anyway)

I would always use DROP/CREATE. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> You should probably put in a Profiler to monitor if they are being dropped/recreated by something during the day behari. We do this anyway for auditing purposes. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

Thank you guys,
I see that most likely the problem is dropping and creating objects.’
For now, since my database is still on the development phase, I will create a script for granting the permissions to that role, so whenever contractor brings me the script with updated procedures, a will also execute my script. Does anyone agree with my idea?

You could tell the contractor to include the permissions (provided there’s no security issue here).

]]>