June 9, 2010 at 12:53 am
hi, I creat a stored procedure, and I want creat a user that has access only to this stored procedure. my problem is that this procedure update values of of extended properties of all databases.
so I dont know what is the minimal premission this user should has.
this is the stored procedure :
create PROCEDURE ywork3 @application nvarchar(100),@contact nvarchar(100),@domain nvarchar(100),@db_name nvarchar(100)
AS
declare @STR nvarchar(4000)
set @STR = 'USE '+@db_name+';'
+' if ''Application'' in (select name from sys.extended_properties)'
+' EXEC sp_updateextendedproperty @name=''Application'',@value='''+@application+''';'
+' else EXEC sp_addextendedproperty @name=''Application'',@value='''+@application+''';'
+' if ''Domain'' in (select name from sys.extended_properties)'
+' EXEC sp_updateextendedproperty @name=''Domain'',@value='''+@domain+''';'
+' else EXEC sp_addextendedproperty @name=''Domain'',@value='''+@domain+''';'
+' if ''Contact'' in (select name from sys.extended_properties)'
+' EXEC sp_updateextendedproperty @name=''Contact'',@value='''+@contact+''';'
+' else EXEC sp_addextendedproperty @name=''Contact'',@value='''+@contact+''';'
EXEC sp_executesql @STR
June 9, 2010 at 9:05 am
Becuase you are updating extended properties I beleive you would have to run this as a user with Admin rights to each database you plan to update.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 9, 2010 at 9:16 am
Best practices dictates:
Use the EXECUTE AS clause in the stored procedure, and have it execute as a user with the proper permissions.
Create a role, and give it access to the stored procedure. Add the appropriate users to that role.
Now, those users will be able to run the stored procedure, which will run properly, but will not have access to modify the table extended properties themselves.
Hope that helps.
--J
June 9, 2010 at 1:17 pm
I read you advice. but I mean that the user will be able only to update the table of extended properties.
June 9, 2010 at 1:27 pm
Ok, well I misunderstood but I think what I said still applies...
In order to modify extended properties, you need ALTER or CONTROL permission on the table in question. Those permissions are going to give your users more permission than you want (certainly a lot more that just the extended properties). To limit them to only extended properties, you would have to allow them access to the extended properties through stored procedures only. Those stored procedures would use WITH EXECUTE AS a user that has ALTER or CONTROL permission on the table(s). Give them permission to the stored procedures. They can now modify the extended properties of the table(s), but not the structure (or any of the other things ALTER/CONTROL permission gives you).
Does that answer your question?
--J
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply