December 28, 2018 at 3:22 pm
I have granted the permission to store proc to role and the user. However, after the store proc is executed then user is loosing the permissions. I have checked in the store proc and it does not have any revoke permission statements. No triggers noticed. What could be the problem. Anyone ever faced this problem? Not sure where else to look?
Thanks
December 28, 2018 at 5:16 pm
it sounds like the procedure is being dropped and recreated. that would lose all permissions in the way you describe, but appear as if the permission was revoked, instead of dropped with the object.
prove it easily, and add a comment in the current definition, with your name or something.
when the permissions disappear, see if it is the SAME procedure, with your comment included or not.
To determine what is dropping and recreating,the default trace can help with that, if not too much time has passed,to confirm whether the object was dropped and created, and at one time.
another way is you could add a database trigger to rollback any DDL commands, and see which process is actually dropping and recreating the procedure, if that is the issue, and of course, if it raises an error that is visible to processes.
Lowell
December 31, 2018 at 10:16 am
Ok.I found the problem is that developer is dropping and recreating the store procedure. He says that he is routinely drop and then recompile procedures. I suggested it is not necessary in MS SQL. What do you say? Since it's dev server so i granted him to following
GRANT CREATE VIEW TO ;
GRANT CREATE PROCEDURE TO ;
GRANT ALTER ON SCHEMA::[dbo] TO ;
December 31, 2018 at 10:24 am
in my case, in all my databases, i create a custom role, and grant some developer group access to that role:IF NOT EXISTS(SELECT 1 FROM sys.database_principals dp WHERE dp.name = 'db_execute' AND dp.type = 'R' )
CREATE ROLE [db_execute] AUTHORIZATION [dbo];
GRANT EXECUTE TO [db_execute];
Lowell
December 31, 2018 at 10:34 am
Thanks. What happens when developer drop and recreate the object. Do you have to Grant execute to the role again?
December 31, 2018 at 11:04 am
i would resolve it forever.
granting execute to the role without limitations like the script did, grants that right to all objects current and created in the future, so you would no longer have to re-apply permissions.
if you followed a difficult practice, like granted execute for each object, ie
GRANT EXECUTE ON dbo.proc TO db_execute;
GRANT EXECUTE ON dbo.proc2 TO db_execute;
that would be the headache scenario, where you have to keep applying/re-applying permissions. permissions
Lowell
December 31, 2018 at 12:31 pm
My question would be why is he dropping and recreating the procedure instead of using ALTER PROCEDURE?
December 31, 2018 at 1:16 pm
If he has a script for his stored procedure he can make it so it just alters the stored procedure if it already exists or creates it then alters it if it doesn't. That way the permissions will be preserved.
IF OBJECT_ID('dbo.myStoredProcedure','P') IS NULL
EXEC ('CREATE PROCEDURE dbo.myStoredProcedure AS')
GO
-- SP
ALTER PROCEDURE dbo.myStoredProcedure
(
@Parm1 varchar(20),
@Parm2 int
) AS
BEGIN
SELECT * FROM myTable
END
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply