To give a user the ability to execute a stored procedure (EXECUTE AS) I must first grant him the ability to impersonate the 'sa'.
(that procedure creates new login and adds to role which a normal user can not execute)
Am I right ?
But when I give him the ability to impersonate 'sa' is not that a security risk ?
Or is the impersonation valid ONLY for that stored procedure ???
Not correct. The stored procedure needs EXECUTE AS OWNER, the owner of the database should be someone or something with 'SA' Privs (normally, the disabled SA login), and the only priv the user will need is to be able to execute the stored procedure.
If you're using something like xp_CmdShell in the proc (for example), the xp_CmdShell proxy will likely need to be setup but no one other than trusted DBAs should ever have privs to call it directly. It should only be done through well written and safe stored procedures as outlined above.
No non-DBA users should ever be granted any elevated privs... period.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)