Web Page for resetting SQL Logins

Last Post 30 Aug 2008 02:22 PM by TRACEYSQL. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
TRACEYSQL
New Member
New Member

--
30 Aug 2008 02:22 PM
I have a sp that i like to use in a web page which allows only logins that exist in a financial database
which is the USER_ID table and allow users to reset their own passwords.

The webpage has to check user login for ssn (last 4 digits, email etc) then calls this with the new password.

The login that is used in web page is DOMAIN/weblogin

Now i have the database
ADM which has ADM.usp_Reset i have given exec rights to the ADM.usp_Reset and when the
page is fired i get permissions denied. I have included securityadmin for the DOMAIN/weblogin
and this worked.

However i would perfer not to set up the securityadmin as this mean the DOMAIN/weblogin can infact
come into the SQL and add a user etc .

How else can i allow procedure below to be fired but only this sp.

Cheers




create procedure [ADM].[usp_Reset]

@Password nvarchar(20),
@username nvarchar(20)

as

DECLARE @SQLCMD NVARCHAR(MAX)
DECLARE @dmessage NVARCHAR(MAX)



DECLARE @CheckLoginName nvarchar(20), @CheckUserName nvarchar(20), @CheckDefaultSchemaName nvarchar(20)


SELECT @CheckLoginName = s.name,
@CheckUserName = d.name,
@CheckDefaultSchemaName = default_schema_name
FROM FINAPP.sys.server_principals s
JOIN FINAPP.sys.database_principals d
ON d.sid = s.sid
INNER JOIN FINAPP..USER_ID e ON s.name = e.user_id
WHERE s.Name not in ('SA','FINSETUP','FINAMAIN','SYSSQL')
and s.Name = d.Name
and s.type = 'S'
and s.Name = @username and d.name = @username

if @CheckLoginName = @CheckUserName and
@CheckLoginName = @CheckDefaultSchemaName

begin


BEGIN
Set @dmessage = ''
-- Alter login
IF @password != '' and @username != 'FINSETUP' and @username != 'FINAMAIN'
Begin
SET @sqlcmd = 'ALTER login ' + quotename(@username) + ' with password = ' + quotename(@password, '''');
EXEC (@sqlcmd);
IF @@error <> 0
BEGIN
SET @dmessage = @@error;
END
ELSE
BEGIN
SET @dmessage = 'success';
END
END
SELECT @dmessage

END

END


Acceptable Use Policy
---