Sql-server – Scripting an application role with a hashed password

I need to script an application role with a hashed password, so I can copy it from one database to another.

Consider the following example code, which uses an Application Role to provide elevated access to a non-trusted user:

USE tempdb;

CREATE LOGIN LimitedLogin
WITH PASSWORD = 'Password1'
    , CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF;

CREATE USER LimitedLogin
FOR LOGIN LimitedLogin
WITH DEFAULT_SCHEMA = dbo;

CREATE APPLICATION ROLE MyAppRole
WITH PASSWORD = 'Password2'
    , DEFAULT_SCHEMA = dbo;

EXEC sp_addrolemember @rolename = 'db_datareader'
    , @membername = 'MyAppRole';

CREATE TABLE dbo.Numbers
(
    [Number] int CONSTRAINT PK_Numbers 
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1) NOT NULL
);

INSERT INTO dbo.Numbers
VALUES (1)
    , (2);

GO

Once we've created the test setup in tempdb, we can login as the [LimitedLogin] user, and run the following:

-- login as [LimitedLogin]

USE tempdb;

SELECT *
FROM dbo.Numbers;

The following error is returned, as expected:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Numbers'
    , database 'Test', schema 'dbo'.

However, once we execute the sp_setapprole with the appropriate password, we can see the desired results from the dbo.Numbers table:

DECLARE @cookie VARBINARY(8000);
EXEC sp_setapprole @rolename = 'MyAppRole'
    , @password = 'Password2'
    , @fCreateCookie = 1
    , @cookie = @cookie OUT;
SELECT @cookie;

SELECT TOP(10) *
FROM dbo.Numbers;

EXEC sp_unsetapprole @cookie = @cookie;

I'd like to be able to automate creating the Application Role by scripting it from a source database to be applied to a destination database. I can easily perform the majority of that by:

SELECT 'CREATE APPLICATION ROLE ' + QUOTENAME(dp.name) + '
WITH PASSWORD = ''xxxx''
    , DEFAULT_SCHEMA = ' + QUOTENAME(dp.default_schema_name) + ';'
FROM sys.database_principals dp
WHERE dp.type_desc = 'APPLICATION_ROLE';

However, I'd really like to be able to have the hashed password in the script, something like:

CREATE APPLICATION ROLE [MyAppRole]
WITH PASSWORD = 0x12345678 HASHED
    , DEFAULT_SCHEMA = [dbo];

Is this possible? Presumably the hashed version of the application role password is stored somewhere in the database.

Using the SQL Server Management Studio "script to ->" functionality, the Application Role is scripted with a new password, as:

/****** Object:  ApplicationRole [MyAppRole]    Script Date: 9/22/2015 10:18:12 AM ******/
/* To avoid disclosure of passwords, the password is generated in script. */
declare @idx as int
declare @randomPwd as nvarchar(64)
declare @rnd as float
select @idx = 0
select @randomPwd = N''
select @rnd = rand((@@CPU_BUSY % 100) + ((@@IDLE % 100) * 100) + 
       (DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))
while @idx < 64
begin
   select @randomPwd = @randomPwd + char((cast((@rnd * 83) as int) + 43))
   select @idx = @idx + 1
select @rnd = rand()
end
declare @statement nvarchar(4000)
select @statement = N'CREATE APPLICATION ROLE [MyAppRole] WITH DEFAULT_SCHEMA = [dbo], ' + N'PASSWORD = N' + QUOTENAME(@randomPwd,'''')
EXEC dbo.sp_executesql @statement
GO

Clearly, that is not helpful in my case, although it does provide an interesting method of generating random passwords.

Based on the answers so far, I have created a Connect suggestion to add support for this to the product:

• CREATE APPLICATION ROLE should support the 'HASHED' syntax for passwords.