Hi Folks,

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.

more ▼

asked Jun 24, 2010 at 08:03 AM in Default

OmAyR gravatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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).

more ▼

answered Jun 24, 2010 at 08:30 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

+1: definitely - why bother trying to reinvent the wheel when AD gives you what you want!
Jun 24, 2010 at 09:20 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 24, 2010 at 08:18 AM

David 1 gravatar image

David 1
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 24, 2010 at 08:03 AM

Seen: 1406 times

Last Updated: Jun 24, 2010 at 08:03 AM