Tips for you on how to create a web installer for your sql scripts

In a project that I just finished, I created a web installer for users to re-create our application's database based on the .sql scripts we gerneated from the sql server.  Today, I came across Scott 's new post in regards to the RunSQL.aspx provided by the Database Publishing Wizard

The runsql.aspx falls short on one important thing.

Of all the scripts generated by the publishing wizard, they fail to grant access to your custom store procedures. (Although they did that for the default aspnet_* views and store procedures)

"Edit -" Please Note, in a shared hosting, often, even after they gave you DBO permission, the store procedures created by the publishing wizard will not automatically grant you execute rights, such, the need for this article.

So if you install the scripts generated by the Publishing Wizard on your remote host (which will install just fine), however, if you try to execute anyone of them, you'll get for example, something like

EXECUTE permission denied on object 'GetAllFromUserData', database 'liming_db', owner 'dbo'.

The reason is you have to explicitly Grant acccess to your db user before they can execute it.

so for those of you who tries to use runsql.aspx, you need to add one more function to the page and hook it up AFTER the default script Simply replace the variable "your_db_username" with a value of your db user name and "GetDatabaseConnectionString()" with your connection string, then you'll be fine.  (Note, the script I used here is based on Brian Lockwood 's article "Use Store Procedure To Manage Sql Security".

What it does is it loops through all the available store procedures in the system (except system procedures, vss procedures and aspnet procedures) and grant access to your db user.

Hope that helps.

 protected bool GrantPermissionToUser()
    {
        bool success = false;
       
        //replace with your own
        String your_db_username = "liming";
    
        String sql =    "DECLARE @ExecSQL varchar(100) " + Environment.NewLine +
                        "DECLARE curGrants CURSOR FOR " + Environment.NewLine +
                        "SELECT 'GRANT EXECUTE ON ' + NAME + ' TO "+your_db_username+"'  " + Environment.NewLine +
                        "FROM SYSOBJECTS " + Environment.NewLine +
                        "WHERE TYPE = 'P' " + Environment.NewLine +
                        "AND LEFT(NAME,2) <> 'sp'  " + Environment.NewLine +
                        "AND LEFT(NAME,2) <> 'dt'  " + Environment.NewLine +
                        "AND LEFT(NAME,6) <> 'aspnet' " + Environment.NewLine +
                        "OPEN curGrants " + Environment.NewLine +
                        "FETCH NEXT FROM curGrants INTO @ExecSQL " + Environment.NewLine +
                        "WHILE @@FETCH_STATUS = 0 " + Environment.NewLine +
                        "BEGIN  " + Environment.NewLine +
                        "    Exec(@ExecSQL) " + Environment.NewLine +
                        "FETCH NEXT FROM curGrants INTO @ExecSQL  " + Environment.NewLine +
                        "END " + Environment.NewLine +
                        "CLOSE curGrants " + Environment.NewLine +
                        "DEALLOCATE curGrants ";
  
        //Replace "GetDatabaseConnectionString()" with your connection string

        using (SqlConnection connection = new SqlConnection(GetDatabaseConnectionString()))
        {
            connection.Open();

            SqlCommand cmd = new SqlCommand(sql, connection);
            cmd.CommandType = CommandType.Text;
            int i = 0;
            try
            {
                i = cmd.ExecuteNonQuery();
                success = true;
            }
            catch (Exception e)
            {
               //catch your exception here
            }
        }

        return success;
    }