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

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 with most of the permission
issues.

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

-Eric Isaacs

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_addrolemember db_owner, approlle



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.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