Application Roles and SQL Server 2005 - DDL?

[JimL]

Hello group,

We have recently come up with a problem where SQLServer 2005 differs
from 2000.

We have an application role, which needs to run DDL to alter tables
etc. The documentation for 2005 states quite clearly that application
roles cannot be made members of built in roles like db_owner or
db_ddladmin - which proves to be the case, but this was possible in
2000.

How are we to give application roles these rights in 2005 (and, for
all I know 2008)?

Thanks,

Jim

[Eric Isaacs]

Re: Application Roles and SQL Server 2005 - DDL?

When you create an object such as a stored procedure, try declaring it
WITH EXECUTE AS OWNER. If dbo owns the procedure, it will execute as
dbo. Anyone that has permission to run that sproc has the required
permission. Just make sure any other spocs it calls also have this
permission declared. That should help with most of the permission
issues.

http://www.mssqltips.com/tip.asp?tip=1227

-Eric Isaacs

[Erland Sommarskog]

Re: Application Roles and SQL Server 2005 - DDL?

JimL (jmlawton@gmail .com) writes:

We have recently come up with a problem where SQLServer 2005 differs
from 2000.
>
We have an application role, which needs to run DDL to alter tables
etc. The documentation for 2005 states quite clearly that application
roles cannot be made members of built in roles like db_owner or
db_ddladmin - which proves to be the case, but this was possible in
2000.
>
How are we to give application roles these rights in 2005 (and, for
all I know 2008)?

Not sure that I understand. This worked fine for me on SQL 2005:

create application role approlle with password = 'rolle'
exec sp_addrolemembe r db_owner, approlle



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx