Fixed Database Roles vs Application Roles

After reading Books Online, I am still confused with Database Role vs Application role.

My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.

If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?

Application roles prevent users from having direct permission on the database, and force them to access it via the application. If you grant permission to the users directly, then if you have lots of users, you will have lots of permissions to maintain. Further, with direct permissions, the user could start accessing the database through interfaces such as Microsoft Access which you may not want them to do.

Typically, application roles are used by applications that perform their own user authentication.

Hope this helps.

|||So, if I want users to access business data ONLY from my client application, I should use application roles. However, if I use application roles, does it mean that I don't have to assign any database role to an user? i.e. can I rely totally on application roles plus schema? Besides, what is the best practise to store the password of application roles in my application?|||

Yes. That's correct. If you use application roles, you just assign permissions to that app role. The application then activates it on connection. You don't need to do anything with user permissions.

As far as the password goes, one approach is to store the password encrypted in the registry on the application server. The application decrypts it before calling the setapprole procedure. This allows you to avoid storing the password in application code and allows you to change the password as well without having to recompile the app.

|||

Great questions. I'm new to AppRoles and am looking a good, simple example, showing how to use them, including the necessary setup on sql 2005 to make it all work. Anyone have a suggested link?

TIA,

barkingdog

P.S. BOL is accurate but too fragmented to be useful to a begineer. After I understand how do to something, then I usually appreciate what BOL is saying.

|||

Question:

Why use an application role over using a SQL User account for the application?

thanks,

Nate

|||

You can actually use a SQL account as well, but in previous versions of SQL Server the impersonation features were not as rich as they are in SQL Server 2005, so application roles used to be the only available way. Now you could as well perform an EXECUTE AS with NO REVERT and that would pretty much achieve what you get from using an application role. Also see the CREATE USER WITHOUT LOGIN statement for creating a principal that is sand-boxed to a database.

Raul also wrote an interesting post on users without login, which you might find useful: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx.

Thanks
Laurentiu

|||

This is great! thank you for the information.

Now my final question!

I have an application in which I want the SQL Profiler will see which user is executing what stored procedure or query, but those queries or stored procedures should only be executed from my application, and fail if they try to execute the query from another database app (some of my users have Query Analyzer)

AKA an application should be able to execute the stored procedure as the Logged in user (windows authentication), but if the same user using windows authentication connects to the same database using another application, like query analyzer, they will be denied. So when my DBA looks into the database, they can see in the application field in Query Profiler that Jimmy is executing sp_GetData from "Application1" (I have specified this in the connection string) but Paul is getting denied from executing sp_GetData from the "Query Analyzer" application.

Thanks for your help,

Nate

|||

This is currently not possible, as there is no mechanism available for authenticating applications.

Thanks

Laurentiu

|||I upgraded a db from SQL Server 2000 sp4 to SQL Server 2005 sp2. I've got a legacy app that had an application role associated with it. When I upgraded my db and attempted to launch my application, only one of my log-ins works. It's ironic because that login is not special. It isn't sysadmin or anything like that. In this old app, all the logins required dbo rights to the database and the app itself stuck them into the application role. I tried deleting and recreating my users and that didn't help. I tried a new user and that didn't help either. The application role converted but it isn't associated with a valid schema. (The default schema assigned to it doesn't exist.) Is it possible for an application role to become orphaned? I had problems with a few orphaned users when i was moving my databases over for testing, but I followed microsoft's instructions for dealing with orphaned users and it worked great. I'm stumped.|||

Application roles shouldn’t become orphaned. The root cause of the orphaned users is that the associated SID is invalid after restoring the DB on a different server (because the corresponding login doesn’t exist on the new server).Application roles have a SID, but it is not linked to master DB (or to any other DB), and it is possible to set a default schema on them as well (you can use ALTER APPLICATION ROLE syntax to change the default schema).

It may be possible that the application has a different case for the password, in SQL Server 2005 passwords are case sensitive. If this is the case, try resetting the password using ALTER APPLCIATION ROLE.

http://msdn2.microsoft.com/en-us/library/ms188900.aspx

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

The password for this application role is actually generated in script. See below. That default schema (xyz_user) doesn't exist when I look in SSMS. When I right-click on the application role and select properties, it shows the password as ****'s for security. What's strange is that it says the schemas owned by this role are "db_owner" and "xyz_user." Yet, when I expand the schemas folder, xyz_user isn't there. I tried creating it again, but that didn't help.

/****** Object: ApplicationRole [xyz_user] Script Date: 07/25/2007 13:51:07 ******/

/* 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 [xyz_user] WITH DEFAULT_SCHEMA = [xyz_user], ' + N'PASSWORD = N' + QUOTENAME(@.randomPwd,'''')

EXEC dbo.sp_executesql @.statement

|||

That is indeed very strange. Can you try to repro it on a new (i.e. test) DB? I am suspecting there is something in the database that is affecting your results.

I tried on SQL Server 2005 Sp2, and I got no schemas owned by [user_xyz] (as expected), and no schema named [user_xyz] (again as expected).

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine