By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

Application Roles and SQL Server 2005 - DDL?

P: n/a
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

Sep 30 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Sep 30 '08 #2

P: n/a
JimL (jm******@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, es****@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

Sep 30 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.