Create Database Scoped Credential using variables for username (Identity) and password (Secret)-CodePudding

I have a stored procedure on an Azure SQL server that is involved in the cleanup process after copying databases to another environment. In the SP I have something like

CREATE DATABASE SCOPED CREDENTIAL MyCredential
    WITH IDENTITY = 'SomeUsername',
    SECRET = 'SomePassword'

I don't want to store these credentials inside the SP so I thought it would be smart to do it like

CREATE PROCEDURE [dbo].[sp_someTask](@username nvarchar(20),@password nvarchar(50))
AS
[...]
CREATE DATABASE SCOPED CREDENTIAL MyCredential
    WITH IDENTITY = @username,
    SECRET = @password
[...]

and provide the credentials in an external script that is calling the SP. But now I get an error

SQL Error [102] [S0001]: Incorrect syntax near '@username'.

Any suggestions on how I can use dynamic credentials here?

CodePudding user response：

Make sure your quotations are necessary around your secret and everything must fit on a single line or be joined by

CREATE PROCEDURE [dbo].[sp_someTask1]
(@username nvarchar(20),
@password nvarchar(50))
AS
BEGIN

DECLARE @command varchar(MAX)

SET @command =     
'CREATE DATABASE SCOPED CREDENTIAL MyCredential '  
'WITH IDENTITY = '''  @username   ''''   
',SECRET = '''  @password   ''''
EXECUTE (@command);

PRINT 'Database Scoped Credential Created'

END

for execution and checking execution completed or not

EXECUTE [dbo].[sp_someTask1] 'user','123456789';

select * from sys.database_scoped_credentials

OUTPUT Scoped Credentials are created successfully.