I have designed a role which will be assigned to every user who connects through application but i dont want to give my code to application Team (they will hardcode the code in their application)
I can activate application role with below mentioned code
EXEC sp_setapprole 'test_role', 'abc@123'; GO
i dont want to share my password abc@123 with application team.
how can i achieve this please help me.
asked Jun 24 '10 at 08:03 AM in Default
I don't generally use the application role either. Mostly we rely on AD groups to define groups of users and then database roles to define security within the database. Adding the AD groups to a given database role grants the privileges we want to grant easily to sets of people. That way, no passwords are used or needed (except those defined at the user level).
answered Jun 24 '10 at 08:30 AM
Grant Fritchey ♦♦
Application roles only provide an extremely weak method of authentication and are of very limited value in my opinion. They are basically just another "security by obscurity" hack. If the issue is only that you don't want to share the password with the developers then why not use a different password in the dev environment? You could have the password entered at install time.
Of course if you are serious about security then use Windows auth or other methods to authenticate the connection at the server.
answered Jun 24 '10 at 08:18 AM