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
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: