Application role

Question

Application role

newbieuser

SSCrazy Eights

Points: 9423
More actions
July 21, 2010 at 8:12 am

#237982

Hi friends,
I need some help to create an application role for the QA database and grant this role 'read only' on few tables for specific users. This users when they login via application, this role will be enabled.
I created the application role on management studio as below:
create APPLICATION ROLE test WITH PASSWORD ='password1' , DEFAULT_SCHEMA = dbo;
When I try to set the approle, I get this error
EXEC sp_setapprole 'test', 'password1'
Msg 15161, Level 16, State 1, Procedure sp_setapprole, Line 46
Cannot set application role 'test' because it does not exist or the password is incorrect.
When I right click on the approle 'test' properties, and select a schema owned by this role, when I go back to the properties again the schema is checked but I'm not able to uncheck it and select a different schema..
Any suggestions please? I'm not sure if I'm doing this right.. I also searched in msdn to see if there is any step by step procedure to accomplish this but cant find it..
Thanks much

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

are you running sp_setapprole in the same database where the app role was created? If you are, I'm not sure why you're getting that error. If there was a password complexity check issue, that should have fired on the create.

K. Brian Kelley
@kbriankelley

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 2

Hi Brian,

I'm executing it on the same database, not sure why its failing.. Do I need to login as sa user for this?

Thanks so much

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 3

I got it to work.. But I'm having a different problem now..

create APPLICATION ROLE test WITH PASSWORD ='password1' , DEFAULT_SCHEMA = dbo;

EXEC sp_setapprole 'test', 'password1'

I'm able to create and exec the approle test successfully. In a new session, when I right click on properties in the approle test, I selected 'aris' under Owned schemas and clicked ok. But from another session or from the same session, when I right click on approle test, I'm not able to uncheck 'aris' schema.. IS it not possible to change the owned schemas in the approle?

Thanks