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: