CODEWITHSUNDEEP
sqlsql-servert-sqlazure-sql-database
Aileron79
Aileron79

Reputation: 897

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

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?

Upvotes: 1

Views: 1243

Answers (1)

Pratik Lad
Pratik Lad

Reputation: 8291

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.

enter image description here

Upvotes: 1

Related Questions