none
Need to execute ALTER DATABASE inside stored procedure RRS feed

  • Question

  • SQL 2005 Standard.

    I wrote a sp that uses ALTER DATABASE instruction. When I execute sp with elevated user it works fine; when I try to execute that sp with a normal user (I alerady gave execute permission for that user) it fails with "alter database failed".

    I tried to modify create proc with execute as owner but nothing changed.

    How can I do ?

    Thanks.

    Friday, October 8, 2010 1:39 PM

Answers

  • Permissions to run the included commands when you give EXEC permission on a proc mainly apply to normal DML commands: SELECT, INSERT, UPDATE, DELETE...

    Permission to ALTER a database is not inherited just because you have EXEC permission on the procedure. 

    You can create the procedure to execute completely in the context of the procedure owner or in the context of another high privilege user. See http://msdn.microsoft.com/en-us/library/ms178106.aspx


    HTH, Kalen Delaney www.SQLServerInternals.com
    Friday, October 8, 2010 1:58 PM
    Moderator

All replies

  • Permissions to run the included commands when you give EXEC permission on a proc mainly apply to normal DML commands: SELECT, INSERT, UPDATE, DELETE...

    Permission to ALTER a database is not inherited just because you have EXEC permission on the procedure. 

    You can create the procedure to execute completely in the context of the procedure owner or in the context of another high privilege user. See http://msdn.microsoft.com/en-us/library/ms178106.aspx


    HTH, Kalen Delaney www.SQLServerInternals.com
    Friday, October 8, 2010 1:58 PM
    Moderator
  • You gave to use dynamic SQL to do it

    create

     

    procedure sp_a

    as

    begin

     

    declare @stmt nvarchar(max)

     

    set @stmt = N'ALTER DATABASE AdventureWorks SET READ_ONLY'

     

    exec sp_executesql @stmt

    end

    exec

     

    sp_a


    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    Friday, October 8, 2010 2:02 PM
  • Hi Krystian

    Did you test this? Commands within an EXEC are even more tightly controlled. Even DML statements have to have explicit permission granted in order to be run in a procedure when embedded in an EXEC.

    This solution will not work.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Friday, October 8, 2010 2:13 PM
    Moderator
  • Hi Kalen,

    unfortunately it does not work. I use SQL 2005 Standard on Win Server 2003.

    I write here steps I do:

    1) Log in management studio using a local administrative user (not sa or administrator but a user MyUser that is member of administrators group of the machine)

    2) create a stored procedure in a db [dbfrom] and custom schema (not dbo) that does some stuff and alter a database [dbto]

    3) assign execute permission to a user "LowUser" that is datareader, datawriter and ddladmin for the database [dbfrom] and [dbto]

    4) Log in management studio in [dbfrom] using "LowUser" and try to execute the previously created sp ... I obtain an error: ALTER DATABASE FAILED

    5) Try to modify sp (logging in as sysadmin) using "with execute as self" because when I create sp I am logged in as sysadmin

    6) Log in management studio using "LowUser" and try again to executesp .. I obtain error: The server principal [MyUser] is not able to access the database [dbfrom] under the current security context.

    Is it possible to achieve or do I have to assign db_owner permission to "LowUser" ?

    Thanks !!

    Friday, October 8, 2010 2:33 PM
  • Sorry Kry it does not work.
    Friday, October 8, 2010 2:34 PM
  • Hi,

    According to your first error message, I think we need to assign db_owner permission to the “LowUser” login.

    The members of db_owner database role can perform any activity in the database. The members of db_accessadmin database role can add or remove Windows NT groups, users or SQL Server users in the database. The members of db_datareader database role can see any data from all user tables in the database. The members of db_datawriter database role can add, change, or delete data from all user tables in the database.

    According to your second error message, the following link describes the error message and provides the solution, please see:

    http://www.sqlmusings.com/2008/06/12/issue-server-principal-is-not-able-to-access-the-database-under-the-current-security-context/

     

    For more information, please see:

    Microsoft SQL Server roles

     

    Thanks,

    Ai-Hua Qiu


    Constant dropping wears away a stone.
    Tuesday, October 12, 2010 7:55 AM