November 2, 2010 at 10:40 am
I've created a stored procedure that allows one of my database admins to change login passwords for his database users only. To accomplish that, I placed this condition at the beginning of the stored procedure:
if not exists(select name from sys.server_principals where name = @lname and default_database_name = 'financials')
begin
print 'Login [' + @lname + '] does not exist for ''financials'' database, cannot change password'
return
end
@lname is the login passed into the procedure. I created a certificate, created a login from the cert and gave the login the permission to alter other logins and then signed the procedure.
If I remove the above condition, the procedure works. But, removing the above condition allows for any login password to be changed, which is bad news.
Is there a way to grant permission to my certificate user to see the sys.server_principals view? I tried VIEW ANY DEFINITION, didn't work.
TIA
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
November 3, 2010 at 9:00 am
I've either stumped everyone or this was a stupid question.
Edit: found my answer, hopefully this helps someone else:
http://database.itags.org/sql-server/38366/
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply